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:
However, I have multiple worksheets and the range for each worksheet varies as well:
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
I appreciate any help on how I should go about this