Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: Hide Blank Rows Across Multiple Worksheets

  1. #1
    Join Date
    12th June 2008
    Posts
    2

    Hide Blank Rows Across Multiple Worksheets

    Hello,

    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:

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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    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.
    AAE
    ----------------------------------------------------

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

  3. #3
    Join Date
    12th June 2008
    Posts
    2

    Re: Hide Blank Rows Across Multiple Worksheets

    Awesome, thank you

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Hide Blank Rows
    By Emmy in forum EXCEL HELP
    Replies: 6
    Last Post: September 24th, 2007, 16:44
  2. Hide Blank Rows Based On Values In Other Rows
    By clange2 in forum EXCEL HELP
    Replies: 2
    Last Post: November 3rd, 2006, 15:47
  3. Hide blank rows when printing
    By BobbyM in forum EXCEL HELP
    Replies: 1
    Last Post: July 21st, 2006, 23:43
  4. Hide Rows with blank cell
    By eldad in forum EXCEL HELP
    Replies: 10
    Last Post: July 12th, 2005, 00:30
  5. hide rows if blank !!!!!!!
    By kallafi in forum EXCEL HELP
    Replies: 2
    Last Post: January 6th, 2005, 19:55

Bookmarks

Posting Permissions

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