1

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.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
Trung Tran
  • 13,141
  • 42
  • 113
  • 200

2 Answers2

4

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))
PatricK
  • 6,375
  • 1
  • 21
  • 25
  • + 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](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba). 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 Nov 08 '13 at 05:20
  • 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... – PatricK Nov 11 '13 at 02:28
0

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)
shree.pat18
  • 21,449
  • 3
  • 43
  • 63
  • 2
    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` :) – Siddharth Rout Nov 08 '13 at 05:22
  • 1
    @Siddharth - Thanks! This was a rushed and somewhat haphazard POC, but I'll keep this in mind! – shree.pat18 Nov 08 '13 at 10:09
  • 1
    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 Nov 11 '13 at 02:07