No announcement yet.

Hide Blank Rows Across Multiple Worksheets

  • Filter
  • Time
  • Show
Clear All
new posts

  • Hide Blank Rows Across Multiple Worksheets


    I have a workbook that consists of:
    • 1 worksheet containing all master data
    • 5 worksheets that pull data from the master data worksheet to display to the user
    • 1 worksheet where the user can make selections on which data to see (selection on Country and Product Group)

    The 5 worksheets display data using IF calculations. As a result, the cells show either records from the master data worksheet or blank ("").

    Now, this obviously creates "gaps" in the display worksheets where users will have to scroll past blank rows to see the data. This is why I'd like to have these blank rows hidden so that all the information is immediately visible and bundled together.

    I've managed to get this working for a single worksheet using the code below:

    Sub HideRows()
        Dim rRange As Range, rCell As Range
        Dim strVal As String
        Set rRange = Worksheets("DATA_DISPLAY").Range("A4:A5000")
        For Each rCell In rRange
            strVal = rCell & rCell(1, 2) & rCell(1, 3) & rCell(1, 4)
            rCell.EntireRow.Hidden = strVal = vbNullString
        Next rCell
         'A, B, C & D are empty.
    End Sub
    However, I have multiple worksheets and the range for each worksheet varies as well:
    (1) A4:A1000
    (2) A4:A3000
    (3) A4:A30
    (4) A4:100
    (5) A4:A250

    I appreciate any help on how I should go about this

  • #2
    Re: Hiding Blank Rows On Multiple Worksheets

    You could use Auto-Filter to filter out the blanks, then save this as a Custom View (see View > Custom View on the menubar), which you can then reference in a macro to recall the view when that sheet is activated. You will need to do this for each applicable worksheet.

    To see the code required to call the Custon View, record a macro going through the steps.

    With Auto-filter you won't need to worry about the variation in size of the range.

    Forum Rules | Message to Cross Posters | How to use Tags


    • #3
      Re: Hide Blank Rows Across Multiple Worksheets

      Awesome, thank you