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