Range as a Variable VBA

Question

I am trying to copy values from sheet1 to sheet2. The number of rows of sheet1 varies so I need to store it in a variable. I need something like:

Worksheets("Sheet1").Range("A2:Ab").Select

, where "b" is a variable that stores the number of rows in Sheet1.

Thank you.

Answer 1

You can actually store the UsedRange of a worksheet. Or just copy to another sheet directly. e.g.

Set oWS1 = Worksheets("Sheet1")
Set oWS2 = Worksheets("Sheet2")
oWS2.UsedRange.Clear ' Clear used range in Sheet2
oWS1.UsedRange.Copy oWS2.Range("A1") ' Copies used range of Sheet1 to A1 of Sheet2

'Set oRng = oWS1.UsedRange ' Sets a Range reference to UsedRange of Sheet1

To get the last row of a sheet:

lLastRow = oWS1.UsedRange.SpecialCells(xlLastCell).Row

EDIT (Getting Last Row from UsedRange's Address):

Dim sAddr as String, lLastRow As Long
sAddr = oWS1.UsedRange.Address
lLastRow = CLng(Mid(sAddr, InStrRev(sAddr , "$") + 1))

Answer 2

One way would be to dynamically create the cell name by concatenating your column and row identifiers and then using the Range(Cell1,Cell2) overload, as below:

Dim rng As Range
Dim intStart As Integer
Dim intEnd As Integer
Dim strStart As String
Dim strEnd As String

'Let's say your range is A2 to A10
intStart = 2
intEnd = 10
strStart = "A" & intStart
strEnd = "A" & intEnd

Set x = Range(strStart, strEnd)