hide empty rows???

[rainxking]

I have a huge spread sheet and I'd like to have a script that scanned the cells in colum A starting at A16 and going down. If the cell is empty or zero I'd like that row to be hidden.

so basically if i have info in A18 A234 and A333. Rather than scroll all over, I'd like to only see those rows. But I still need rows 1-15 on top.

All help is greatly appreciated!!!

 

[jindon]

Hi

try the code

Sub hide()
Dim r As Range
Application.ScreenUpdating = False
With ActiveSheet
    For Each r In .Range("a16", Range("a65536").End(xlUp))
        If IsEmpty(r) Or r.Value = 0 Then
            r.EntireRow.Hidden = True
        End If
    Next
End With
Application.ScreenUpdating = True
End Sub
Sub show()
With ActiveSheet
    .Cells.EntireRow.Hidden = False
End With
End Sub

hope this helps

jindon

 

[rainxking]

no thats not working. I am running excel 2002. I don't think your script went down to A16. the only information that needs to be looked at is in column A. If any cell in A (after A15) is empy than hide that row.

Thanks

 

[jindon]

Hi

did you know there are 2 subroutines. Sub hide() and Sub show()

Sub hide() should do the trick

whereas Sub sho() will unhide all rows again

or maybe the cell contains "" then try

Sub hide()
Dim r As Range
Application.ScreenUpdating = False
With ActiveSheet
    For Each r In .Range("a16", Range("a65536").End(xlUp))
        If IsEmpty(r) Or r.Value = 0 Or r.Value = "" Then
            r.EntireRow.Hidden = True
        End If
    Next
End With
Application.ScreenUpdating = True
End Sub

rgds,

jindon

 

[rainxking]

this works great thank you.
The only thing is that if all the cells are empty after 250 it does hide those. make sense? so it hides all the lines until the last cell with info and then everything after it still shows. dig?

 

[jindon]

Hi

if you want to change the range then

change
.Range("a16", Range("a65536").End(xlUp)

to
.Range("a16:a250") * alter a250 to suite

rgds,
jindon

 

[rainxking]

i dont think i was clear.

i need to check the whole sheet. so thats fine. Here the deal. Say I have all my info in column A ends at cell a25. the macro only hides the blank cells between a16 and a25. it still leaves all rows from a26 down to the end. I have a info in the other columns, but I don;t need them because I am only concerned with rows that have info in collumn A.

Does that make more sense?

 

[jindon]

Hi,

Yes, clear!

Sub hide()
Dim r As Range, firstR As Long, lastR As Long
Application.ScreenUpdating = False
With ActiveSheet
    firstR = .UsedRange.Row - 1
    lastR = .UsedRange.Rows.Count
    For Each r In .Range("a16:a" & firstR + lastR)
        If IsEmpty(r) Or r.Value = 0 Or r.Value = "" Then
            r.EntireRow.Hidden = True
        End If
    Next
End With
Application.ScreenUpdating = True
End Sub

rgds,
jindon

 

[rainxking]

awesome
thanks

 

[Barry Katcher]

Try this. Enter something, such as the word End, in cell A65536.

Then run this macro:

Sub FilterForNonBlanks()
    Range("A16:A65536").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:="<>"
End Sub

This will give you row 1 thru your last row with an entry in column A, followed by row 65536, followed by a big gray area on the remainder of the screen.

and this code to unfilter:

Sub UnFilter()
    Selection.AutoFilter
End Sub

Barry