Hiding unused rows
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Hiding unused rows

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Wivenhoe, England
    Posts
    912
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I want automatically to hide rows that contain no data just before printing.
    A cell in the row to be hidden in Sheet 2 contains the formula: =IF(Sheet1!A15=0,"",Sheet1!A15) so we need to look at the result and not that the cell contains nothing. Users will enter figures on Sheet 1 which are then formatted and totalled on Sheet 2.
    If cells A15 and A16 on Sheet 1 are left blank, I want the corresponding rows (not necessarily rows 15 and 16) in Sheet 2 to reduce to zero width.
    I think I need a macro with a series of If Then operations but have been struggling with my very basic knowledge of VBA.
    Users might need to go back later and put information in A15 or A16 or both so the macro would have to unhide (and preferably autofit the relevant rows in Sheet 2) when run again. I would also like to restore normal row heights to the rows on Sheet 2 on exit from the worksheet.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    I don't know what the corresponding rows are?
    I would put a simple macro in the Workbook_BeforePrint event or Worksheet_Change on sheet1 which would hide the rows. Then restore the rows in the Worksheet_Change on sheet1, testing for an added value in A1.
    How do you know which rows to hide???
    Maybe you could change your formula to:
    =IF(Sheet1!A15=0,"HideRow",Sheet1!A15)
    Which columns contains these formulas?
    Tom

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Wivenhoe, England
    Posts
    912
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The corresponding rows are the ones left blank on Sheet 2 if A15 and A16 are empty but which will contain the contents of cell A15 and cell A16 on sheet 1 otherwise. The reference to corresponding rows was so that the macro can select which rows to hide. If someone can kindly give me the basic code, I should be able to edit it to hide the relevant rows on Sheet 2.

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    'To hide rows 6 & 7
    Rows("6:7").EntireRow.Hidden = True
    'To hide row 6 only
    Rows("6:6").EntireRow.Hidden = True

    Change the true to false to unhide

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Location
    Wivenhoe, England
    Posts
    912
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank you Tom. I may not be making myself clear. I need the If Then bit as well and presumably an If Then to unhide on a repeat pass if figures are later put in cells A15 and A16.

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    No you are not making yourself clear...
    If then what?
    There are 256 cells in one row.
    Which column(s) intersecting the rows in question holds the determining value.
    If A5, B5, C5 = "" then hiderow?
    How many rows deep? 65536?
    Please give more detail...
    Thanks,
    Tom



  7. #7
    Board Regular
    Join Date
    Apr 2002
    Location
    Wivenhoe, England
    Posts
    912
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry,
    For the sake of the example, Cell B18 on Sheet 2 will contain the formula =Sheet1!A15. Cell B20 on Sheet 2 will contain the formula =Sheet1!A16. If cell A15 on Sheet 1 is empty then hide Row 18 on Sheet 2. If cell A16 on Sheet 1 is empty then hide Row 20 on Sheet 2.

    The user might go back to Sheet 1 from Sheet 2 and add data to A15 and/or A16. If the user is undecided he or she might go back again and clear the cells again.

    So on re-running the macro, if cell A15 on Sheet 1 is not empty then autosize Row 18 on Sheet 2. If cell A16 on Sheet 1 is not empty then autosize Row 20 on Sheet 2.

    I will attach the macro to a button taking the user from Sheet 1 to Sheet 2. Therefore the macro will run each time and needs to test whether cells A15 or A16 are empty or not. The user will see that rows 18 and 20 on Sheet 2 will be hidden or unhidden depending on whether anything has been put in A15 and A16 on Sheet 1.

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Gotcha!
    I hope

    Sub RowStuff()
    Dim RowCntr As Long
    For RowCntr = 1 To 1000 'or increase number of rows if necc.
    If Sheet2.Range("B" & RowCntr).Value = "" Then
    'if the value of B? = "" then hide row
    Sheet2.Rows(RowCntr & ":" & RowCntr).EntireRow.Hidden = True
    Else
    'if the value of B? <> "" then un-hide & autofit row
    Sheet2.Rows(RowCntr & ":" & RowCntr).EntireRow.Hidden = False
    Sheet2.Rows(RowCntr & ":" & RowCntr).EntireRow.AutoFit
    'if you would rather set to a predetermined row height use:
    'Sheet2.Rows(RowCntr & ":" & RowCntr).RowHeight = 7.5
    End If
    Next
    End Sub

    Tom

  9. #9
    Board Regular
    Join Date
    Apr 2002
    Location
    Wivenhoe, England
    Posts
    912
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank you for all the time you are taking but we are not quite there yet. I don't want to hide all rows except those corresponding to rows in Sheet 1 with stuff in them. I want to hide only the single rows concerned. Thus if there is nothing in A15 in Sheet 1, row 18 in Sheet 2 (but no others) will be hidden. I gave the example of two rows because I might wish to make it 3 or 4 and the code may be different from that for just a single row.
    I am hiding the rows so that when Sheet 2 is printed I avoid what would otherwise be acres of blank space. Thanks

  10. #10
    Board Regular
    Join Date
    Apr 2002
    Location
    Wivenhoe, England
    Posts
    912
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Playing around with your code (and with a bit of help from the macro recorder) I have come up with the following code:

    Sub RowStuff()
    If Sheet1.Range("A15").Value = "" Then
    Sheet2.Rows("18:18").EntireRow.Hidden = True
    Else
    Sheet2.Rows("18:18").EntireRow.Hidden = False
    Sheet2.Rows("18:18").EntireRow.AutoFit

    End If

    End Sub

    How do I get it to repeat to hide other rows, eg if A16 is empty to hide Row 20 on sheet 2? I expect the answer is very simple.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com