VBA Help - Hide/Unhide Rows if Column is blank Loop

[AF1988]

Hello,

I was hoping someone could help out please? I've searched through plenty of threads to find an answer but can't seem to find it.

I have a spreadsheet containing Array VLOOKUPS that display either a 0 or data in the cell range B24:I223 when an account code is entered in to Cell K2.

I have the code displayed below that hides any rows where 0 is in the cell range B24:B223, and so only displays the rows that have data in them.

What I really need is for this to effectively rerun when Cell K2 is updated with a different account code, and then un-hide any rows with a non-0 value, and hide any that does contain 0.

To top it off, this needs to be a quick run too as I tried using a Worksheet_Change (ByVal Target as Range) but this was way way too slow.

Option Explicit
Private Sub Worksheet_Activate()
Dim r As Range, c As Range
Set r = Range("B24:B223")
Application.ScreenUpdating = False
For Each c In r.Rows
If Len(c.Cells(1).Text) + Len(c.Cells(2).Text) = 0 Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c
Application.ScreenUpdating = True
End Sub

Thank you in advance to anyone that can help!

Kind regards,
AF1988

 

[AF1988]

Hi Fluff,

The range would be B24:L223

Many thanks.

Kind regards,
AF1988

 

[Fluff]

Is Row 24 the header row, or the first set of data?

 

[AF1988]

Row 24 is the first set of date, row 23 is the header.

Many thanks!

 

[Fluff]

In that case try

Private Sub Worksheet_Change(ByVal Target As Range)
   Application.ScreenUpdating = True
   Application.Calculation = xlCalculationManual
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "K2" Then
      Range("B23:L223").AutoFilter 1, "<>"
   End If
   Application.Calculation = xlCalculationAutomatic
End Sub