Is there any way to dynamically select an excel column using VBA?

Question

Task: I need to copy a cell value from E12 to the range E23:E25. I achieved this easily using a simple copy and paste statement in the editor. But the range isn't always fixed. I have managed to capture the start point of this range as: Set rangeStart = Range("E12").End(xlDown).Offset(6, 0). I am unable to use this as a starting point for a range selection statement as follows:

Range("E23:E" & Range("A23").End(xlDown).Row).Select

That is how I'm selecting the range to be filled with data in the next step via a paste statement. How do I edit the first half of that range call to something more dynamic? Using rangeStart instead of E23:E.

I have just started working with Excel VBA, so this might be a very basic question to ask. Any help would be appreciated.

First, you should examine [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). 101% of answer to your question is there. — Vitaliy Prushak, Feb 05 '20 at 09:36

score 0 · Answer 1 · answered Feb 05 '20 at 09:41

0

If you have

Set rangeStart = Range("E12").End(xlDown).Offset(6, 0)

You can also have

Set rangeEnd = Range(rangeStart, Range("E" & Range("A23").End(xlDown).Row)

which will form a range from the first cell to the second cell.

But read the link posted by Vitaliy.

answered Feb 05 '20 at 09:41

SJR

22,986
6
18
26

This helped too. `Range("E" & Range("A23").End(xlDown).Row)` is the exact solution I was looking for. Thanks much. – Vivek Menon Feb 05 '20 at 11:08

score 0 · Accepted Answer · answered Feb 05 '20 at 09:47

First I'd like to address the point that using Select is in most cases highly discouraged. Since you're only looking to paste a value into other cells, you can simply use a line like the following example:
Range("A1:A10").Value = Range("C2:C11").Value
or
Range("A1:A10").Value = 1
This would fill the A1:A10 range with either the values from C2:C11 or the integer 1, respectively. Note the two ranges in the first line are of the same size.

Now, a dynamic approach to your problem could be something along the following example

'Initialise two range variables
Dim SourceRange As Range, TargetRange As Range
'And integers for target rows
Dim fRow As Long, lRow As Long

With ThisWorkbook 'Assuming the code needs to be performed on the Workbook that hosts the macro
    'Determine target rows
    fRow = 23 'You have not specified how the first row should be determined
    lRow = .Cells(fRow, "E").End(xlDown).Row

    'Populate range vars
    Set SourceRange = .Range("E12")
    Set TargetRange = .Range(.Cells(fRow, "E"),.Cells(lRow, "E"))

    'Paste values to target range
    TargetRange.Value = SourceRange.Value
End With

Note that, since you haven't specified how this should be determined instead, the code above is hard-coded to have a target range in column E that starts at row 23.

Thank you! Your code snippet also helped in clarifying the underlying problem that I was facing - which includes the excessive use of the `Select` command. This dynamic approach is what I will be employing in such situations going forward. — Vivek Menon, Feb 05 '20 at 11:05

Is there any way to dynamically select an excel column using VBA?

2 Answers2