Excel VBA defining a .Range with .Cells

Question

I'm running into a problem trying to select/copy certain ranges within my worksheet. I've narrowed it down as best as I can.

This works fine:

dataSheet.Cells(dRow, dataSheet.Range("IO_MODULES").Column).Select

This does not:

dataSheet.Range(Cells(dRow, dataSheet.Range("IO_MODULES").Column), Cells(dRow, dataSheet.Range("IO_MODULES").Column)).Select

This is what I'm trying to do:

dataSheet.Range(Cells(dRow, dataSheet.Range("IO_MODULES").Column), Cells(dRow, dataSheet.Range("MODULE_END").Column)).Copy Destination:= _
dataSheet.Range(Cells(dataSheet.Range("MODULE_1").Row + i - 1, dataSheet.Range("D_COUNT").Column + 1), Cells(dataSheet.Range("MODULE_1").Row + i - 1, dataSheet.Range("DATA_COL_END").Column))

I seem to be misunderstanding the relationship between Worksheet.Cells and Worksheet.Range. What I need to do is be able to select a range of cells based on the row and column numbers of cells that I've named.

On the whole, you're going to want to try and avoid using .Select -- here's a nice write-up about that: stackoverflow.com/questions/10714251/… — Dan Wagner
Cells is an unqualified range object, so it always is implicitly defined as ActiveSheet.Cells. Therefore, you get an error with dataSheet.Range(Cells(... unless dataSheet is the ActiveSheet. — David Zemens
Wow, can't believe I overlooked that. Not sure what I was thinking using Cells like I was. Thanks — Neat Machine

Dan Wagner Dan Wagner · Accepted Answer · 2014-05-27T15:45:32

UNTESTED try this to copy from one Range to another:

'...
Dim Source As Range, Dest As Range

With dataSheet
    Set Source = .Range(.Cells(dRow, .Range("IO_MODULES").Column), _
        .Cells(dRow, .Range("MODULE_END").Column))
    Set Dest = .Range(.Cells(.Range("MODULE_1").Row + i - 1, .Range("D_COUNT").Column + 1), _
        .Cells(.Range("MODULE_1").Row + i - 1, .Range("DATA_COL_END").Column))
    Source.Copy Dest
End With

The With...End With is in place to allow you to operate many times on a single object (in this case, dataSheet) without calling it explicitly every time. Every line of code you write is a line that must be maintained and potentially debugged, so when you can be more concise without losing readability you should do so. (More about With...End With statements here: http://msdn.microsoft.com/en-us/library/office/gg264723(v=office.15).aspx)
By naming the Ranges you can use the super-friendly Range.Copy method, outlined here: http://msdn.microsoft.com/en-us/library/office/ff837760(v=office.15).aspx. This enables you to bypass a .Select or .Activate, which in my experience is a major source of run-time errors.

Excel VBA defining a .Range with .Cells

1 Answers