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

 

[Fluff]

Hi & welcome to MrExcel.
You're description & code do not match up. Which is correct?

 

[AF1988]

Hi & welcome to MrExcel.
You're description & code do not match up. Which is correct?

Hello,

Thank you!
It's the description that I'm looking for a solution on. Something that runs quickly to only display rows where B24:B223 contains anything other than 0, which changes when the value in K2 is amended and the Enter key is hit.

Many thanks
AF1988

 

[Fluff]

In that case try

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Cl As Range
   Application.ScreenUpdating = True
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "K2" Then
      For Each Cl In Range("B24:B223")
         Cl.EntireRow.Hidden = Cl.Value = 0
      Next Cl
   End If
End Sub

 

[AF1988]

In that case try

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Cl As Range
   Application.ScreenUpdating = True
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "K2" Then
      For Each Cl In Range("B24:B223")
         Cl.EntireRow.Hidden = Cl.Value = 0
      Next Cl
   End If
End Sub

Thank you for the quick response. There is an array formula sitting in each of the B column cells, so your code isn't hiding those rows (I presume because the formula is there).
It also runs fairly slowly, is there any way for a similar code to run any quicker?

Many thanks again,
AF1988

 

[Fluff]

If the formula returns 0 (a number not a string) then the code should work.
If it's not working then I suspect that the formula is not returning a number =0

 

[AF1988]

If the formula returns 0 (a number not a string) then the code should work.
If it's not working then I suspect that the formula is not returning a number =0

Hi Fluff,

I've amended your code slightly to Cl.EntireRow.Hidden = Cl.Value = "" and this works a treat, I just need to figure a way to speed it up.

Many thanks for your help.

Kind regards,
AF1988

 

[Fluff]

Assuming that calculation is set at Automatic, try adding these two lines

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Cl As Range
   Application.ScreenUpdating = True
   [COLOR=#ff0000]Application.Calculation = xlCalculationManual[/COLOR]
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "K2" Then
      For Each Cl In Range("B24:B223")
         Cl.EntireRow.Hidden = Cl.Value = ""
      Next Cl
   End If
  [COLOR=#ff0000] Application.Calculation = xlCalculationAutomatic[/COLOR]
End Sub

 

[AF1988]

Hi Fluff,

That's much quicker, thank you!!

This has now posed another issue unfortunately. There is an Autofilter on the column heads to then allow the user to manually hide certain rows should they not want it displayed (For example if the invoice is not yet overdue).

Is there a way to then hide the rows removed by Autofilter?

Sorry to be a pain!

Thank you for all of your help!

Kind regards
AF1988

 

[Fluff]

What is the range of the autofilter?