Help with code

[Bobby]

Hi
My sheet is laid out as follows,
A1 Unit1
A5 Unit2
A9 Unit3
etc. through A21Unit6

As a new unit is added the cellis changed to the actual unit name. The 3
lines after each unit are associated with that particular unit.I need code
that when run will hide the rows for any units that havent been named.
Example
A1= Dog
A5 = Cat
units 3-6 have not been added. When the macro is run I want to hide rows
9-24.
I hope I have provided enough information
All help would be greatly appreciated.
Thanks!

[Ken Johnson]

Hi Bobby,
Will this suffice?

Public Sub HideUnNamed()
Dim I As Long
For I = 1 To 21 Step 4
If Cells(I, 1).Value = "" Then
Range(Cells(I, 1), Cells(I + 3, 1)).EntireRow.Hidden = True
End If
Next I
End Sub

Ken Johnson

[Ken Johnson]

Hi Bobby,
I don't think it works because the unnamed cells aren't blank, they
have "Unit#", so try...

Public Sub HideUnNamed()
Dim I As Long
For I = 1 To 21 Step 4
If Left(Cells(I, 1).Value, 4) = "Unit" Then
Range(Cells(I, 1), Cells(I + 3, 1)).EntireRow.Hidden = True
End If
Next I
End Sub

Ken Johnson

[Bobby]

That works great. How would I modify to assign to a button and toggle
show/hide?
Thanks!

"Ken Johnson" wrote:

> Hi Bobby,
> Will this suffice?
>
> Public Sub HideUnNamed()
> Dim I As Long
> For I = 1 To 21 Step 4
> If Cells(I, 1).Value = "" Then
> Range(Cells(I, 1), Cells(I + 3, 1)).EntireRow.Hidden = True
> End If
> Next I
> End Sub
>
> Ken Johnson
>
>

[Ken Johnson]

I would assign the macro to a button from the Forms toolbar.
Go View>Toolbars>Forms then click on the button button then click on
the worksheet.
Right click the button>Assign macro etc
Right Click the button to Edit its caption
If you want toggling to occur it will take a bit more code, which I'll
have to look into.
Ken Johnson

[Ken Johnson]

Hi Bobby,
for toggling show/hide try this macro:

Public Sub ShowHideUnNamedToggle()
Application.ScreenUpdating = False
Dim I As Long
Dim Hidden As Boolean
For I = 1 To 24
If Rows(I).EntireRow.Hidden Then
Hidden = True
Rows(I).EntireRow.Hidden = False
End If
Next I
If Hidden Then Exit Sub
For I = 1 To 21 Step 4
If Left(Cells(I, 1).Value, 4) = "Unit" Then
Range(Cells(I, 1), Cells(I + 3, 1)).EntireRow.Hidden = True
End If
Next I
End Sub

Assign macro to a button from the Forms toolbar, add the caption
"show/hide"

GoodLuck
Ken Johnson