Go to special

Closed
Viking57 - Jul 16, 2010 at 11:59 AM
aquarelle
Posts
7115
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 2, 2022
- Jul 23, 2010 at 01:51 AM
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

Blocked Profile
Jul 16, 2010 at 02:10 PM
Hi there,

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

Thanks
0
office 2007
0
aquarelle
Posts
7115
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 2, 2022
491
Jul 16, 2010 at 03:06 PM
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."
0
Marco stops at .Rows (i) . EntireRow . Hide
0
aquarelle
Posts
7115
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 2, 2022
491
Jul 16, 2010 at 04:20 PM
Yes, because I made a mistake but I edited my message to modify my macro and now I think it is OK :)
0
I tried the revised Macro, it goes through the motions, but does not hide cells with formulas.
0
aquarelle
Posts
7115
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 2, 2022
491
Jul 17, 2010 at 02:16 PM
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."
0
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","")
0
aquarelle
Posts
7115
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 2, 2022
491
Jul 23, 2010 at 01:51 AM
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
0