Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Kuala Lumpur , MALAYSIA
    Posts
    154
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hiding empty rows before printing.. (Excel97)

    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

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding empty rows before printing.. (Excel97)

    Will this do what you want?

    <pre>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
    </pre>

    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Kuala Lumpur , MALAYSIA
    Posts
    154
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding empty rows before printing.. (Excel97)

    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

  4. #4
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Kuala Lumpur , MALAYSIA
    Posts
    154
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding empty rows before printing.. (Excel97)

    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

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Hiding empty rows before printing.. (Excel97)

    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

  6. #6
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Kuala Lumpur , MALAYSIA
    Posts
    154
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding empty rows before printing.. (Excel97)

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

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding empty rows before printing.. (Excel97)

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

    <pre>Sub Unhide()
    Range("A1:A150").EntireRow.Hidden = False
    End Sub
    </pre>

    Legare Coleman

  8. #8
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Kuala Lumpur , MALAYSIA
    Posts
    154
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding empty rows before printing.. (Excel97)

    Thanks legare..take care

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •