Creating a variable range

PamH · #1 (**permalink**) 06-30-06, 17:17

I'm trying to create a routine that will allow me to use a variable range but am not having much luck. Can anyone tell me what I might be misunderstanding here?
<<<<<<<<<<<< = differing lines....

strColLtr = ConvertColumnNumberToLetter(LastCol%)
strRangeToCopy = """" & "A1:" & strColLtr & LastRow& & """"
rngMyRng = strRangeToCopy
Sht.Range(strRangeToCopy).Select <<<<<<<<<<<<<<<<<<<<<<
Selection.Copy

when I use this, it works?
strColLtr = ConvertColumnNumberToLetter(LastCol%)
strRangeToCopy = """" & "A1:" & strColLtr & LastRow& & """"
rngMyRng = strRangeToCopy
Sht.Range("A1:G41").Select <<<<<<<<<<<<<<<<<<<<<<<<
Selection.Copy

norie · #2 (**permalink**) 07-01-06, 11:23

Where are you defining LastRow?

By the way you don't actually need the column letter.

MikeTheBike · #3 (**permalink**) 07-03-06, 09:01

Hi

I don't know if this helps, but you could try this, using the cells object

Code:

Sub Test()
Dim LastCol As Long
Dim LastRow As Long

LastCol = 6
LastRow = 22

ActiveSheet.Range(Cells(1, 1), Cells(LastRow, LastCol)).Select
Selection.Copy

End Sub

OR even this

Code:

Sub Test()
Dim RangeAddress As String
Dim LastCol As Long
Dim LastRow As Long

LastCol = 6
LastRow = 22

ActiveSheet.Range("A1", Cells(LastRow, LastCol)).Select
Selection.Copy

End Sub

Does that help ?

MTB

PamH · #4 (**permalink**) 07-05-06, 10:55

Mike,
Thanks - this worked fine. I had defined my LastRow & LastCol elsewhere and tried converting them to a string to use. It appears that I can not use the string to pass to a range definition? I keep getting hung up on that from the record macro process....
Thanks again, Pam

norie · #5 (**permalink**) 07-05-06, 11:13

Pam

You can use a string to specify the range, the problem with your code could be because the string you created actually contained unneeded quotes.

MikeroSoft · #6 (**permalink**) 07-05-06, 13:43

With ActiveSheet.UsedRange
MsgBox .Columns.Count
MsgBox .Rows.Count
End With

Internet Services

Web Development

Digital Marketing

Consumer Tech