Go to special

[Closed]
Report
-
Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
-
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



3 replies


Hi there,

Which version of office are you currently using?Please mention .

Thanks
office 2007
Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
488
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."
Marco stops at .Rows (i) . EntireRow . Hide
Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
488
Yes, because I made a mistake but I edited my message to modify my macro and now I think it is OK :)
I tried the revised Macro, it goes through the motions, but does not hide cells with formulas.
Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
488
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."
i really appreciate your help however marco still does not work, here is the formula in the cells i am trying to hide =if(c1>1,"K","")
Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
488
Hi,

Please could you load a brief example of your excel file (without any private or personal data) on https://authentification.site then copy past the created link to your next answer.

See you