How to set the range object in Excel for VBA

Both Hans and Ron have shown how to do it with

Range(Cells(row,col), Cells(row,col))  

type referencing of the range - which is fast and effecient.  These also fit right in with your (very wise move) of declaring constants for startDataRow and startDataColumn.

If you want to use Range to build a formula that uses column letters and row numbers - those look a little more like what you attempted:

Sub Dummy()

  Const StartCol = "A"

  Const StartRow = 12

  Const LastCol = "R" ' if you happen to know it

  Dim LastRow As Long

  Dim RangeToCopy As Range

'assuming column A has entries for all used rows

  LastRow = Range(StartCol & Rows.Count).End(xlUp).Row

  Set RangeToCopy=Range(StartCol & StartRow & ":" & LastCol & LastRow)

'do more stuff like copy and paste

'good housekeeping to release resources you SET back to the system

  Set RangeToCopy = Nothing

End Sub

By the way, Ron de Bruin has a really nice function that you can use to determine:

The last row used in any range (from a single column to all cells on the sheet)

The last column used in any range (from single row to all cells on the sheet)

And the address of the intersection of last row and last column.

It is on this page: http://www.rondebruin.nl/win/s9/win005.htm

The combined function is down about 3/4 of the page.

Hans,

   It probably wouldn't make a difference here, but note that your method assumes that Row 1 and subsequent are part of UsedRange.  If the first one or more rows are blank, the desired range may not be returned.

Of course, my method may return a larger range under some circumstances, but shouldn't return a smaller range.

Many ways.

Here's one, with variable declaration required, and using "last cell".  There are other ways to find the last used cell, however.

Dim loCurrentWS As Worksheet

Dim loSourceRange As Range

Const StartDataCol As Long = 1

Const StartDataRow As Long = 12

'Next line only here for testing purposes

'In your macro, it would be set as you iterate through the

'   worksheet loop

Set loCurrentWS = ActiveSheet

With loCurrentWS

    Set loSourceRange = .Range(.Cells(StartDataRow, StartDataCol), .Cells.SpecialCells(xlCellTypeLastCell))

End With

Edit:  I note that you wrote you want to copy only the rows with data.  If the "data" is NOT a result of formulas, you could set that range a bit more robustly by looking for the last row and column with data:

Dim loCurrentWS As Worksheet

Dim loSourceRange As Range

Dim lLastRow As Long, lLastCol As Long

Const StartDataCol As Long = 1

Const StartDataRow As Long = 12

'Next line only here for testing purposes

'In your macro, it would be set as you iterate through the worksheet loop

Set loCurrentWS = ActiveSheet

With loCurrentWS

'cell must contain value

    lLastRow = .Cells.Find(what:="*", after:=[a1], LookIn:=xlValues, _

        searchorder:=xlByRows, searchdirection:=xlPrevious).Row

    lLastCol = .Cells.Find(what:="*", after:=[a1], LookIn:=xlValues, _

        searchorder:=xlByColumns, searchdirection:=xlPrevious).Column

    Set loSourceRange = Range(.Cells(StartDataRow, StartDataCol), .Cells(lLastRow, lLastCol))

End With