Hi Howard,

Am Thu, 5 Dec 2013 22:23:38 -0800 (PST) schrieb Howard:

The commented out lines for rangeF work fine for the sheet with the code in its module. No need to mess with them.

The With Sheets("Data") lines for the rangeH error out after the Msgbox line. The msgbox displays the correct value for the last row on the Data sheet.
Any combination of the lines I have commented out or are using produces an error.

if you don't refer to a sheet the code takes the active sheet. To run
the code from each sheet use 2 With statements.
The code must be in a Standard Module
You can't select a range if the sheet is not active. But activate the
sheet and then select the range is not good. Better use Application.Goto

Sub xx()
Dim rangeF As Range
Dim rangeH As Range
Dim lr As Long
Dim lrH As Long

With Sheets("Sheet1")
lr = .Cells(.Rows.Count, "F").End(xlUp).Row
MsgBox lr
Set rangeF = .Range("F6:F" & lr)
Application.Goto rangeF
End With

With Sheets("Data")
lrH = .Cells(.Rows.Count, "H").End(xlUp).Row
MsgBox lrH
Set rangeH = .Range("H6:H" & lrH)
Application.Goto rangeH
End With

End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2