hide rows according to a value

[avishain]

Hello there,

i need hide some rows if a cell value = 0 .

i need to check from cell "A30" to cell "A11" if the cell = 0 .


the check need to be done backwards,meaning,first check cell A30 and then A29 till A11.


next thing i wanna do , say all the cells from A30 till A16 = 0 and cell A15 = 1000 ,then, i want to hide all the rows from A30 TO A16.


thank you

 

[tigeravatar]

avishain,

Not sure why'd you want to use a loop that goes backwards when you can get all rows containing a 0 at the same time:

Sub tgr()
    
    Dim rngHide As Range
    Static rngAll As Range: Set rngAll = Range("A10:A30")
    
    rngAll.AutoFilter 1, 0
    On Error Resume Next
    Set rngHide = rngAll.Offset(1).Resize(rngAll.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
    rngAll.AutoFilter
    
    If Not rngHide Is Nothing Then rngHide.EntireRow.Hidden = True
    
End Sub

 

[tigeravatar]

avishain,

Occurred to me that even if there are other rows containing 0, you only want the bottom consecutive rows containing 0 to be hidden. Updated the last line of the code to do just that:

Sub tgr()
    
    Dim rngHide As Range
    Static rngAll As Range: Set rngAll = Range("A10:A30")
    
    rngAll.AutoFilter 1, 0
    On Error Resume Next
    Set rngHide = rngAll.Offset(1).Resize(rngAll.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
    rngAll.AutoFilter
    
    If Not rngHide Is Nothing Then Range(StrReverse(Split(StrReverse(rngHide.Address), ",")(0))).EntireRow.Hidden = True
    
End Sub

Hope that helps,
~tigeravatar

 

[avishain]

avishain,

Not sure why'd you want to use a loop that goes backwards when you can get all rows containing a 0 at the same time:

Sub tgr()
 
    Dim rngHide As Range
    Static rngAll As Range: Set rngAll = Range("A10:A30")
 
    rngAll.AutoFilter 1, 0
    On Error Resume Next
    Set rngHide = rngAll.Offset(1).Resize(rngAll.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
    rngAll.AutoFilter
 
    If Not rngHide Is Nothing Then rngHide.EntireRow.Hidden = True
 
End Sub

thank you very much!! works perfect.


what was the code if i would accept your seggastion?

 

[tigeravatar]

what was the code if i would accept your seggastion?

I'm not sure what you mean here. You quoted my whole post including the code I suggested...

 

[avishain]

I'm not sure what you mean here. You quoted my whole post including the code I suggested...

i didnt see you second code which works more than perfect for me... i really appriciate your help.

i got a little prbolem with the macro cause my sheet is protected with a password and when i run the macro when the sheet is unprotected- is works perfect, however,when the sheet is protected i got an error.


can you help with that? i work with excel 2007...

 

[tigeravatar]

Just wrap the code in .Unprotect/.Protect. If, for example, the sheet's password was simply password then the code would be:

Sub tgr()
    
    ActiveSheet.Unprotect Password:="password"
    Dim rngHide As Range
    Static rngAll As Range: Set rngAll = Range("A10:A30")
    
    rngAll.AutoFilter 1, 0
    On Error Resume Next
    Set rngHide = rngAll.Offset(1).Resize(rngAll.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
    rngAll.AutoFilter
    
    If Not rngHide Is Nothing Then Range(StrReverse(Split(StrReverse(rngHide.Address), ",")(0))).EntireRow.Hidden = True
    ActiveSheet.Protect Password:="password"
    
End Sub

 

[avishain]

thanks again!! everything works just perfect....


have a nice day