hide empty rows???

rainxking

New Member
Joined
Oct 14, 2004
Messages
14
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!!!
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi

try the code
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

New Member
Joined
Oct 14, 2004
Messages
14
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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
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 Or r.Value = "" Then
            r.EntireRow.Hidden = True
        End If
    Next
End With
Application.ScreenUpdating = True
End Sub

rgds,

jindon
 

rainxking

New Member
Joined
Oct 14, 2004
Messages
14

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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

New Member
Joined
Oct 14, 2004
Messages
14

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi,

Yes, clear!

Code:
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
 

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
Try this. Enter something, such as the word End, in cell A65536.

Then run this macro:

Code:
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:

Code:
Sub UnFilter()
    Selection.AutoFilter
End Sub

Barry
 

Forum statistics

Threads
1,147,675
Messages
5,742,546
Members
423,737
Latest member
tom_xls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top