Macro to Hide Rows with a Condition

[markf5998]

I have a worksheet where AU6:AU55 contain a number which is zero or greater.

I'd like to be able to hide rows where AU6:AU55 = 0, but only after the last time AU6:AU55 was >0.

For example, if [AU6=1, AU7=0,AU8=1] then AU9:AU55 should be hidden and AU7 should remain visible, since there was a >0 after it.

I tried to search for similar problems, but I don't know enough VB to modify code that I found to fit this condition. Thanks for any suggestions!!

 

[stilesbn]

Loops generally aren't the most efficient way to go about things but I can't think of a better way. I would set up a variable that will count down each row and reset every time there is a value greater than 0. So it would look like this:

Sub findLast()

'turn off screen updating
Application.ScreenUpdating = False

Dim counter As Long
Dim keepGoing As Boolean
Dim lastRow As Long


' Assuming your range is always 6-55 start the counter at 6
counter = 6
keepGoing = True

Do While (keepGoing)
    
    'check to see if the value in AU (column 47) is greater than 0
    If Cells(counter, 47) > 0 Then
        lastRow = Cells(counter, 47).Row
    End If
    
    'send the loop to the next row
    counter = counter + 1
    
    'end loop if row is greater than 55.
    If counter >= 56 Then keepGoing = False
    
Loop
    
    ' hide the last row through row 55
    Rows(lastRow & ":55").EntireRow.Hidden = True
    
'turn screen updating back on
Application.ScreenUpdating = True

End Sub

 

[markf5998]

Thanks for the reply! It's giving an error on:

Rows(lastRow & ":55").EntireRow.Hidden = True

Any idea what the problem could be?

 

[stilesbn]

You may have gotten to the code before I edited a typo. Where I have the number 27, every instance of 27 should be replaced with 47.

 

[markf5998]

Awesome, thanks for your help!

I made one other change, as it was hiding the last row with a >0:
' hide the last row through row 55
Rows(lastRow + 1 & ":55").EntireRow.Hidden = True

Thanks again for your time & work! Greatly appreciated!

 

[texasalynn]

does this work?

Range("AU6:AU55").Activate
    Set rFound = Range("AU6:AU55").Find(What:="0", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)
            If rFound Is Nothing Then GoTo 0
    
    rFound.Activate
If ActiveCell.Offset(1, 0) > 0 Then
    i = ActiveCell.Offset(1, 0).Address
    Range(i & ":AU55").EntireRow.Hidden = True
End If
0

 

[chuckles1066]

Just to piggyback off of this, GoTo 0?

I see this in a lot of macros but this is the first bit of code I've seen where there is a physical 0 in the code?

 

[texasalynn]

Goto 0 is a way to force the code to move to where the physical 0 in the code is located.

 

[chuckles1066]

Goto 0 is a way to force the code to move to where the physical 0 in the code is located.

Yep, I was using GoTo's in my BASIC programming days 30 years ago

That bit of code is the first I've seen with an actual 0 for the code to jump to........what happens if you GoTo 0 without a 0 in the code?

 

[texasalynn]

not sure - probably give you an error in VBA???