Go to special

Closed
Viking57 - Jul 16, 2010 at 11:59 AM
aquarelle Posts 7141 Registration date Saturday April 7, 2007 Status Moderator Last seen December 19, 2024 - 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 responses

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

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

Thanks
office 2007
aquarelle Posts 7141 Registration date Saturday April 7, 2007 Status Moderator Last seen December 19, 2024 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."
Marco stops at .Rows (i) . EntireRow . Hide
aquarelle Posts 7141 Registration date Saturday April 7, 2007 Status Moderator Last seen December 19, 2024 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 :)
I tried the revised Macro, it goes through the motions, but does not hide cells with formulas.
aquarelle Posts 7141 Registration date Saturday April 7, 2007 Status Moderator Last seen December 19, 2024 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."
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","")
aquarelle Posts 7141 Registration date Saturday April 7, 2007 Status Moderator Last seen December 19, 2024 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