range function

R

Rick

This example works Range("A3:F" & SheetEnd).Select...
How do I make this one work? Range("A" & SheetStart & ":D" & SheetEnd).Select

The statement has to be as generic as possible.
 
G

Gary''s Student

Sub rick()
Dim SheetStart As Long, SheetEnd As Long
SheetStart = 3
SheetEnd = 11
Range("A" & SheetStart & ":D" & SheetEnd).Select
End Sub
 
R

Rick Rothstein

I'm not sure what your ultimate question is, but the range you asked about
appears to work fine (as long as you have assigned values to both SheetStart
and SheetEnd).
 
B

Bernard Liengme

Have you tried this which worked for me

Sub thisone()
sheetstart = 5
sheetend = 12
Range("A" & sheetstart & ":D" & sheetend).Select
End Sub


For some reason VBA seems to want the & to have spaces each side
 
G

Gary''s Student

Perhaps he means the first and last rows in ActiveSheet.UsedRange??

Sub rick()
Dim SheetStart As Long, SheetEnd As Long
Set r = ActiveSheet.UsedRange
SheetEnd = r.Rows.Count + r.Row - 1
SheetStart = r.Row
Range("A" & SheetStart & ":D" & SheetEnd).Select
End Sub
 
B

Bernard Liengme

This also works -- note the comma not semi-colon with the Range arguments

Sub onemore()
row1 = 5: col1 = 1
row2 = 12: col2 = 4
Range(Cells(row1, col1), Cells(row2, col2)).Select
End Sub

best wishes
 
L

Leith Ross

Rick;189027 said:
This example works Range("A3:F" & SheetEnd).Select...
How do I make this one work? Range("A" & SheetStart & ":D"
SheetEnd).Select

The statement has to be as generic as possible.
Click to expand...

Hello Rick,

The syntax is correct. What error are you getting

--
Leith Ros

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/
 
J

JLGWhiz

This should do it. Since SheetEnd is apparently a cell address, it cannot be
used as a row reference.

Range("A" & SheetStart & ":D" & Range(SheetEnd).Row).Select
 
R

Rick

I was trying to get it to execute, even after compiling. It is working now
in the original format. don't understand why it did not work earlier. Thanks
all for your help
 
Top