Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code
I would assign the macro to a button from the Forms toolbar.
Go ViewToolbarsForms then click on the button button then click on the worksheet. Right click the buttonAssign 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
Generic protect/unprotect code through buttons and code? | Excel Programming | |||
How to make a button VBA code reference other VBA code subroutines??? | Excel Programming | |||
stubborn Excel crash when editing code with code, one solution | Excel Programming |