I created a dummy test Excel worksheet and inserted your VBA code into a new module. It worked fine for me, albeit is a bit clunky.
Some suggestions to help:
- Always set Option Explicit at the top of your module, because this means any undeclared variables and other little things like that get picked up immediately. It's good practice to get into that habit early when starting out with VBA.
- Always qualify your .Range statement with the prefix for the specific workbook/worksheet your code needs to work on. This may be why it isn't working for you, but ran fine for me. As it stands, your code will only run on whatever worksheet happens to be active at the time.
- You have made this a Private Sub (private subroutine). If you have done the proverbial copy & paste then this subroutine will not show up in your list of macros, which could be another reason you cannot run it. I highly recommend you have a read of this ExcelOffTheGrid article, which breaks down the different types nicely. If you have inserted this into the Worksheet object of your VBA Project, then it may need to be moved into its own Module.
- You have assigned three different names (Andrew, Robert, Michael) to the same .Range reference. This shouldn't really be allowed (although weirdly didn't flag an error when I copied your code) because what it is saying is that those text strings - and they could be anything, not just those names - all refer to the same specific cell on your worksheet. This hasn't affected your code, because you don't actually refer to them later on. In your Select Case logical tests you have used double quotes " " around each name, telling VBA it is a string of characters not a variable you have defined.
I would suggest something like this:
COPY & PASTE INTO A NEW MODULE
Option Explicit
'
'
Sub HideRows()
' This macro will hide specific non-contiguous rows based upon criteria in my drop down combo box.
'
Dim wkMyBook As Workbook
Dim wsMainSheet As Worksheet
Dim rName As Range
'
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
Set wkMyBook = ActiveWorkbook
Set wsMainSheet = wkMyBook.Sheets("ENTER SHEET NAME HERE")
Set rName = wsMainSheet.Range("K30")
Select Case rName
Case Is = "Andrew"
wsMainSheet.Rows("8:10").EntireRow.Hidden = True
wsMainSheet.Rows("11:12").EntireRow.Hidden = False
wsMainSheet.Rows("13:13").EntireRow.Hidden = True
wsMainSheet.Rows("14:25").EntireRow.Hidden = False
Case Is = "Robert"
wsMainSheet.Rows("6:20").EntireRow.Hidden = True
wsMainSheet.Rows("21:25").EntireRow.Hidden = False
Case Is = "Michael"
wsMainSheet.Rows("1:5").EntireRow.Hidden = True
wsMainSheet.Rows("6:25").EntireRow.Hidden = False
End Select
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
End Sub
COPY & PASTE INTO THE WORKBOOK OBJECT
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
' This code will run whenever a change is made to your worksheet
If Target.Address = "$K$30" Then
Select Case Target.Value
Case Is = "Andrew", "Robert", "Michael"
Call HideRows
End Select
End If
End Sub
This has bought your ticket, given you the lift, dropped you off right at the door. Now you gotta do that final step to put this together to make it work. Read the article I linked, learn about bit about how VBA is constructed and then next time you should be a bit further along the path before you need a pick up. Good luck!
