range function
 

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.

range function
 

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

--
Gary''s Student - gsnu200827


"Rick" wrote:

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.

range function
 

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).

--
Rick (MVP - Excel)


"Rick" wrote in message
...
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.

range function
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Rick" wrote in message
...
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.

range function
 

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

--
Gary''s Student - gsnu200827


"Rick Rothstein" wrote:

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).

--
Rick (MVP - Excel)


"Rick" wrote in message
...
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.

range function
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Rick" wrote in message
...
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.

range function
 

Rick;189027 Wrote:
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.

Hello Rick,

The syntax is correct. What error are you getting?


--
Leith Ross

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/)
------------------------------------------------------------------------
Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=52110

range function
 

This might work better

Range("A" & SheetStart & ":D" & Right(SheetEnd, 2)).Select




"Rick" wrote:

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.

range function
 

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



"Rick" wrote:

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.

range function
 

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


"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Rick" wrote in message
...
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.