Hiding empty rows before printing.. (Excel97)

Topic

New Reply

Hi again guys..

Need your help again..I got my data from A1:E100..
It is filled with colour , formula and border..
Let say..sometimes I just use only from A1:E50..
So..is there any macro which can detect the empty rows and hide it..so when I print it..I dont want the border to appear..
the formula is ok..it show nothing..coz no data filled

ps: the empty rows still contain formula and border thats why i can’t delete it..

Thanks in advance

Reply | Quote

Replies

Will this do what you want?

Public Sub Test()
Dim I As Long
    For I = 0 To 99
        With Worksheets("Sheet1").Range("A1")
            If (.Offset(I, 0) & .Offset(I, 1) & .Offset(I, 2) & .Offset(I, 3) _
              & .Offset(I, 4)) = "" Then
                .Offset(I, 0).EntireRow.Hidden = True
            End If
        End With
    Next I
End Sub

Reply | Quote

Thanks Legare..it works like charm..so now..
how to make the hidden rows appear again.. I try to change your macro line from :

.Offset(I, 0).EntireRow.Hidden = True
To
.Offset(I, 0).EntireRow.Hidden = False

but its not working..any ideas..thanks a zillion

Reply | Quote

I’ve got the macro to unhide the rows :
Sub Unhide()
Cells.Select
Selection.EntireRow.Hidden = False
End Sub

But the data seems to be copy to all of the
hidden rows although i dont put it..i realize it after i unhide the rows using my dummy macro…seems it still needs your help again..thanks again

Reply | Quote

If you use cell.select, you are selecting the entire worksheet.

Try

Sub Unhide()
Range(“A1:A150”).Select
Selection.EntireRow.Hidden = False
End Sub

Your first solution (reversing Legare’s code), should work unless you managed to alter the values of the hidden rows.

Andrew

Reply | Quote

Thanks and thanks Andrew..you always be my “saviour”..take care andrew..it works!!!

Reply | Quote

Actually, there should be no reason to select the rows. The following should work:

Sub Unhide()
    Range("A1:A150").EntireRow.Hidden = False
End Sub

Reply | Quote

Thanks legare..take care

Reply | Quote