VBA code to hide rows

[mikeb0543]

New member on vbaexpress here. I'm having issue with getting my vba code to run correctly...here is my problem:

What I'm trying to do here is hide certain rows in excel based on different conditions. I have a vlookup in excel which returns a number from 1 to 9. Based on different conditions that are met, it will return a number indicating which rows are needed to hide.

In my vba code, I have a case for each of the numbers 1 to 9, along with the commands to hide the necessary rows. The vlookup in excel seems to be working fine, and the number will change when different conditions are met. The problem I'm having is that when the number changes, it seems that this is not being picked up in the vba code. It will not hide the correct rows after the number has changed. However, if I go to excel and manually type in a different number, the macro will run correctly and hide the correct rows.

I'm not sure if this has made any sense so far. What I'm wondering is if there is a way to add code that will recognize when the number has changed and hide the associate rows for that number?

Thank you in advance and I apologize if this is not very clear.

[CatDaddy]

can i see your code? this is definitely do-able

[austenr]

Sounds like you might need to put your code in the worksheet change event. Cant really tell without seeing the code.

[mikeb0543]

Thanks guys. Here is an example of how the code looks.

[VBA]Sub hide_rows()
Select Case Range("AA110").Value
Case "1"
Sheets("Test").Select
Rows("10:50").Select
Selection.EntireRow.Hidden = False
Rows("30:35").Select
Selection.EntireRow.Hidden = True
Rows("46:48").Select
Selection.EntireRow.Hidden = True
Case "2"
Sheets("Test").Select
Rows("10:50").Select
Selection.EntireRow.Hidden = False
Rows("15:17").Select
Selection.EntireRow.Hidden = True
Rows("32:40").Select
Selection.EntireRow.Hidden = True
Case "3"
Sheets("Test").Select
Rows("10:50").Select
Selection.EntireRow.Hidden = False
Rows("15:17").Select
Selection.EntireRow.Hidden = True
Rows("33:47").Select
Selection.EntireRow.Hidden = True
Case "4"
Sheets("Test").Select
Rows("10:50").Select
Selection.EntireRow.Hidden = False
Rows("43:45").Select
Selection.EntireRow.Hidden = True
Case "5"
Sheets("Test").Select
Rows("10:50").Select
Selection.EntireRow.Hidden = False
Rows("34:36").Select
Selection.EntireRow.Hidden = True
Case "6"
Sheets("Test").Select
Rows("10:50").Select
Selection.EntireRow.Hidden = False
Case "7"
Sheets("Test").Select
Rows("10:50").Select
Selection.EntireRow.Hidden = False
Rows("32:40").Select
Selection.EntireRow.Hidden = True
Case "8"
Sheets("Test").Select
Rows("10:50").Select
Selection.EntireRow.Hidden = False
Rows("17:20").Select
Selection.EntireRow.Hidden = True
Rows("30:33").Select
Selection.EntireRow.Hidden = True
Rows("43:49").Select
Selection.EntireRow.Hidden = True
Case "9"
Sheets("Test").Select
Rows("10:50").Select
Selection.EntireRow.Hidden = False
Rows("17:20").Select
Selection.EntireRow.Hidden = True
Rows("30:33").Select
Selection.EntireRow.Hidden = True
End Select
End Sub[/VBA]

[CatDaddy]

are you running the piece that fills cell AA110 before the rest of this code?

probably your range needs better definition:
[VBA]caseRng As Long
caseRng = Workbooks(somewb) or ActiveWorkbook.Sheets(somesheet).Range("AA110").Value

Select Case caseRng
[/VBA]

[austenr]

Does this macro always fire from the sheet named "Test"? If so then I would strongly suggest you use the Worksheet Change Event. That way, you eliminate testing for the sheet in each Case statement.

Also this will depend on the answer to CD's question.

[mikeb0543]

This is the code that I use to call that macro:

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$AA$110" Then
Call hide_rows
End If
End Sub[/VBA]

I'm guessing this is what you guys are referring to, and hopefully this will answer your question austenr.

[mikeb0543]

Is there something else I need for this to run correctly?

[austenr]

can you post an example workbook with your code as it is?

[CatDaddy]

are you sure the select case Range("AA110").Value is being referenced from the right sheet?!?!?!?!

[mikeb0543]

Here is an example workbook, hopefully it will help.

[CatDaddy]

There doesn's seem to be data in Range("AA110") on any of the sheets you provided...so Range("AA110").Value will equal nothing, so your select case will not fire...

what are you trying to accomplish here?

[mikeb0543]

Range("AA110") does not correspond to the worksheet I've attached. In the attachment, it is actually K110 in the 'End' tab.

[CatDaddy]

so your gonna want to use Sheets("End").Range("K110").Value as your selection statement no?

[Aflatoon]

Your controlling cell is a formula so it will not trigger a change event - you need the Calculate event instead. You can also tidy up the hiderows code:

Sub hide_rows()
   With Sheets("Test")
   
      ' adjust sheet name as required here
      Select Case Sheets("sheet name").Range("AA110").Value
      
         Case 1
            .Rows("10:50").Hidden = False
            .Rows("30:35").Hidden = True
            .Rows("46:48").Hidden = True
         Case 2
            .Rows("10:50").Hidden = False
            .Rows("15:17").Hidden = True
            .Rows("32:40").Hidden = True
         Case 3
            .Rows("10:50").Hidden = False
            .Rows("15:17").Hidden = True
            .Rows("33:47").Hidden = True
         Case 4
            .Rows("10:50").Hidden = False
            .Rows("43:45").Hidden = True
         Case "5"
            .Rows("10:50").Hidden = False
            .Rows("34:36").Hidden = True
         Case "6"
            .Rows("10:50").Hidden = False
         Case "7"
            .Rows("10:50").Hidden = False
            .Rows("32:40").Hidden = True
         Case "8"
            .Rows("10:50").Hidden = False
            .Rows("17:20").Hidden = True
            .Rows("30:33").Hidden = True
            .Rows("43:49").Hidden = True
         Case "9"
            .Rows("10:50").Hidden = False
            .Rows("17:20").Hidden = True
            .Rows("30:33").Hidden = True
            
      End Select 'Case Sheets("sheet name").Range("AA110").Value
      
   End With 'Sheets("Test")
End Sub