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.

Solution

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

Comments

[Tim Williams]

Worksheets("Sheet1").Range("A2:A" & b).Copy Worksheets("Sheet2").Range("A2")

[Siddharth Rout]

+ 1 :) However, just a heads up. UsedRange should never be used to find the last row which has data. You may want to see THIS. Here it is fine since you are just copying from one sheet to other but if the user wanted to write to the last row then UsedRange.SpecialCells(xlLastCell).Row might give you unexpected results :)

[Siddharth Rout]

When working with rows in Excel, it is advisable that you do not declare them as Integers but as Long. To test it, change intEnd = 10 to intEnd = 1048570 :)

[shree.pat18]

@Siddharth - Thanks! This was a rushed and somewhat haphazard POC, but I'll keep this in mind!

[PatricK]

I always use the type from MS Object References, when I am not sure, I use ?typename(...) in Immediate window, e.g. ?typename(range("a1").row) gives Long, you might be surprised that most numeric constants are Long in Excel - ?typename(xllastcell) gives Long.

[PatricK]

Thanks @SiddharthRout, i didn't thought that method will change when the last row is hidden! Your solution works even all the rows are hidden! Alternatively, I think I can make use of the Address of the UsedRange...