Auto Hide/Show rows based on multiple data VBA using Worksheet Activate

Sunshine8790

New Member
Joined
Jun 1, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi all,
Newbie here, just learning about macros and VBAs.
I have a workbook with 3 worksheets in it:
1. Hub (where all my macro enabled buttons are, to pull, edit and delete data on the other sheets)
2. Data sheet (where my data is imported from a separate workbook)
3. Pivot sheet - this is the sheet I'm having difficulty with.

On the pivot sheet, depending on the data imported, sometimes there are blank rows in one of the multiple pivots that say: (blank).
What I am wanting to do is have the sheet automatically hide any rows between a certain set of columns and rows that contain either that term "(blank)" or no value at all.
So, in my example below, I need all the rows with (blank) hidden - and I need all the rows between rows 5 and 28 that contain no data at all (as seen in rows 14-17 - I have 0's in the yellow parts in yellow text to avoid having the yellow hidden) to be hidden.
But since this data will change every time new data is imported to my other tab, I need it to automatically unhide those cells if data populates those same cells.

Does that make sense?

1622551560129.png


So right now, I have a VBA code inserted where you right click on the worksheet itself and hit "view code" that IS working to remove just the cells with no data at all - but I need to also have it remove the (blank) text rows:

Private Sub Worksheet_Activate()

Dim rCheck As Range
Dim rHide As Range
Dim rCheckCell As Range

Set rCheck = ActiveWorkbook.ActiveSheet.Range("B3:B30")
rCheck.EntireRow.Hidden = False

For Each rCheckCell In rCheck.Cells
If InStr(1, rCheckCell, "(blank)", vbTextCompare) > 0 Then
If Not rHide Is Nothing Then Set rHide = Union(rHide, rCheckCell) Else Set rHide = rCheckCell
End If
Next rCheckCell

If Not rHide Is Nothing Then rHide.EntireRow.Hidden = True
stCol = 1
endCol = 10
stRow = 5
endRow = 30
For r = stRow To endRow
counter = 0
For c = stCol To endCol
If Cells(r, c).Value = "" Then
counter = counter + 1
End If
Next c
If counter = endCol Then
Rows(r).EntireRow.Hidden = True
Else
Rows(r).EntireRow.Hidden = False
End If
Next r
End Sub
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Sunshine8790

New Member
Joined
Jun 1, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Solved my own question using the below VBA code:

Private Sub Worksheet_Activate()
Dim r As Long
Dim m As Long
Application.ScreenUpdating = False
Me.Range("B5:B30").EntireRow.Hidden = False
m = Range("B" & Me.Rows.Count).End(xlUp).Row
For r = 1 To m
If Range("B" & r).Value = "" Or Range("B" & r).Value = "(blank)" Then
Range("B" & r).EntireRow.Hidden = True
End If
Next r
Application.ScreenUpdating = True
End Sub

VBA Code:
Private Sub Worksheet_Activate()
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    Me.Range("B5:B30").EntireRow.Hidden = False
    m = Range("B" & Me.Rows.Count).End(xlUp).Row
    For r = 1 To m
        If Range("B" & r).Value = "" Or Range("B" & r).Value = "(blank)" Then
            Range("B" & r).EntireRow.Hidden = True
        End If
    Next r
    Application.ScreenUpdating = True
End Sub
 
Solution

Forum statistics

Threads
1,136,772
Messages
5,677,637
Members
419,707
Latest member
Anna vib

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top