PDA

View Full Version : VBA code to hide rows



mikeb0543
07-13-2011, 11:53 AM
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
07-13-2011, 12:14 PM
can i see your code? this is definitely do-able

austenr
07-13-2011, 12:33 PM
Sounds like you might need to put your code in the worksheet change event. Cant really tell without seeing the code.

mikeb0543
07-13-2011, 12:56 PM
Thanks guys. Here is an example of how the code looks.

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

CatDaddy
07-13-2011, 01:47 PM
are you running the piece that fills cell AA110 before the rest of this code?

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

Select Case caseRng

austenr
07-13-2011, 01:49 PM
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
07-13-2011, 03:51 PM
This is the code that I use to call that macro:

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

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

mikeb0543
07-14-2011, 07:20 PM
Is there something else I need for this to run correctly?

austenr
07-14-2011, 09:07 PM
can you post an example workbook with your code as it is?

CatDaddy
07-15-2011, 10:25 AM
are you sure the select case Range("AA110").Value is being referenced from the right sheet?!?!?!?!

mikeb0543
07-15-2011, 04:07 PM
Here is an example workbook, hopefully it will help.

CatDaddy
07-15-2011, 04:13 PM
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
07-15-2011, 04:46 PM
Range("AA110") does not correspond to the worksheet I've attached. In the attachment, it is actually K110 in the 'End' tab.

CatDaddy
07-18-2011, 12:05 PM
so your gonna want to use Sheets("End").Range("K110").Value as your selection statement no?

Aflatoon
07-19-2011, 05:50 AM
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