Go to specialClosed

Question

Hello, I am tring to us a macro that will hide all rows that are blank, however i have formulas in each cell of the column, so when i us go to special, blanks it will not work due to the formulas, what are my options



Configuration: Windows XP / Internet Explorer 8.0

jaunglia · Answer

Hi there,

Which version of office are you currently using?Please mention .

Thanks

aquarelle · Answer

Hi,    

Try this :    

Sub HidingEmptyRows()    
Dim p As Range, i As Long    
Set p = Application.InputBox(Prompt:="Select a range of cells", _    
Title:=" Hiding of empty rows", Type:=8)    
    With p    
        For i = .Rows.Count To 1 Step -1    
            If Application.CountA(.Rows(i)) = 0 Then _    
            .Rows(i).EntireRow.Hidden = True   
        Next i    
    End With    
End Sub

Best regards    
"Pour trouver une solution à ses problèmes, il faut s'en donner la peine."

aquarelle · Answer

Hi,  

Sorry , I had forgotten the possibility to have cells= 0, try this new one :  

Sub HidingEmptyRows()
Dim p As Range, i As Long
Set p = Application.InputBox(Prompt:="Select a range of cells", _
Title:=" Hiding of empty rows", Type:=8)
    With p
        For i = .Rows.Count To 1 Step -1
            If Application.CountIf(.Rows(i), "=0") > 0 Or _
            Application.CountIf(.Rows(i), "*") = 0 Then _
            .Rows(i).EntireRow.Hidden = True
        Next i
    End With
End Sub 


Best regards  
"Pour trouver une solution à ses problèmes, il faut s'en donner la peine."

Go to special

3 replies

Subscribe To Our Newsletter!