Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with a worksheet that is not the active worksheet
How can I make this code work on my worksheet named February, when it is not
the active worksheet? Sub BlankWeeks() ' ' Macro2 Macro ' ' If Range("C184").Value = "" Then Rows("184:228").Hidden = True End If If Range("C184").Value < "" Then Rows("184:228").Hidden = False End If If Range("C229").Value = "" Then Rows("229:273").Hidden = True End If If Range("C229").Value < "" Then Rows("229:273").Hidden = False End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with a worksheet that is not the active worksheet
Hi,
You have to qualify the ranges with a sheet name Sub BlankWeeks() Set sht = Sheets("February") With sht If .Range("C184").Value = "" Then .Rows("184:228").Hidden = True End If If .Range("C229").Value < "" Then .Rows("229:273").Hidden = False End If End With End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "ordnance1" wrote: How can I make this code work on my worksheet named February, when it is not the active worksheet? Sub BlankWeeks() ' ' Macro2 Macro ' ' If Range("C184").Value = "" Then Rows("184:228").Hidden = True End If If Range("C184").Value < "" Then Rows("184:228").Hidden = False End If If Range("C229").Value = "" Then Rows("229:273").Hidden = True End If If Range("C229").Value < "" Then Rows("229:273").Hidden = False End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with a worksheet that is not the active worksheet
You have to tell the VB processor the name of your worksheet which you can
do using the Worksheets collection. There are two ways to do this. First, by directly qualifying each range reference... Sub BlankWeeks() If Worksheets("February").Range("C184").Value = "" Then Worksheets("February").Rows("184:228").Hidden = True End If If Worksheets("February").Range("C184").Value < "" Then Worksheets("February").Rows("184:228").Hidden = False End If If Worksheets("February").Range("C229").Value = "" Then Worksheets("February").Rows("229:273").Hidden = True End If If Worksheets("February").Range("C229").Value < "" Then Worksheets("February").Rows("229:273").Hidden = False End If End Sub or second (the much cleaner looking way), by using a With/End With block... Sub BlankWeeks() With Worksheets("February") If .Range("C184").Value = "" Then .Rows("184:228").Hidden = True End If If .Range("C184").Value < "" Then .Rows("184:228").Hidden = False End If If .Range("C229").Value = "" Then .Rows("229:273").Hidden = True End If If .Range("C229").Value < "" Then .Rows("229:273").Hidden = False End If End With End Sub Notice the "dots" in front of each range reference (Range, rows, etc.)... those are required in order that the range references back to the object of the With statement. -- Rick (MVP - Excel) "ordnance1" wrote in message ... How can I make this code work on my worksheet named February, when it is not the active worksheet? Sub BlankWeeks() ' ' Macro2 Macro ' ' If Range("C184").Value = "" Then Rows("184:228").Hidden = True End If If Range("C184").Value < "" Then Rows("184:228").Hidden = False End If If Range("C229").Value = "" Then Rows("229:273").Hidden = True End If If Range("C229").Value < "" Then Rows("229:273").Hidden = False End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying the active worksheet to another worksheet in the same work | Excel Worksheet Functions | |||
Deleting column in a worksheet that is not active worksheet | Excel Programming | |||
Basic Question - How do I return the worksheet number of the active worksheet? | Excel Programming | |||
Altering code to reference the worksheet before the active worksheet | Excel Programming | |||
macro to apply worksheet event to active worksheet | Excel Programming |