Prev: How to write a macro to hide an entire row
Next: Disabling copy/cut/paste for shapes
From: ordnance1 on 14 Apr 2010 04:26
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

From: Mike H on 14 Apr 2010 04:50
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
>
From: Rick Rothstein on 14 Apr 2010 10:17
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" <ordnance1(a)comcast.net> wrote in message
news:F65C8F5C-9CF8-415A-8BC7-D3C6A1A9CDE8(a)microsoft.com...
> 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

 | 
Pages: 1
Prev: How to write a macro to hide an entire row
Next: Disabling copy/cut/paste for shapes