VBA Range from a specific row to the full column

Question

I am trying to use VBA to run a macro from a specific cell ("A2") and then apply that to the whole column of ("A").

Just like how you would use a macro normally without VBA, select a cell, enter your formula and click on the bottom-right square to apply it to the full column

I want it from A2 downwards as I have a header in A1, see below comments for a workaround.

Here is my code below

Sheets("PlanningSystemData").Range("A:A").Formula = "=CONCATENATE(D2,E2)"

I attempted the following as I thought this would be an easy fix

Sheets("PlanningSystemData").Range("A2:A").Formula = "=CONCATENATE(D2,E2)"

But, I got the following error "Run-time error '1004':

Application-defined or object-defined error"

Let me know what you think!

Note: I did find a workaround by using the first piece of code in the question, then a loop to push every row in the column down by 1 — EuanM28, Oct 12 '22 at 09:10
`Sheets("PlanningSystemData").Range("A2:A" & LastRow).Formula = "=CONCATENATE(D2,E2)"` and to find last row you can see [Find last used cell in Excel VBA](https://stackoverflow.com/questions/11169445/find-last-used-cell-in-excel-vba) — Siddharth Rout, Oct 12 '22 at 09:14
Feel free post an answer with the code that you tried so that it can help future visitors. — Siddharth Rout, Oct 12 '22 at 09:39

score 1 · Accepted Answer · answered Oct 12 '22 at 10:08

Too long for a comment. Two remarks to the code the OP (EuanM28) posted as answer to his question:

a) Always qualify your sheet - tell VBA which sheet you want to access, else it will work on the ActiveSheet, and that is not only the sheet you want to work with (avoid to use Activate!)
b) When fetching the last row you should always go the other way: Move to the very last cell and go up. Why? Because starting from the first row and go down will result in the very last row of your sheet if used on a blank sheet.

With ThisWorkbook.Sheets("PlanningSystemData")
    lastRowJT2 = .cells(.rows.count, "A").End(xlUp).Row
    If lastRowJT2 > 1 Then
        .Range("A2:A" & lastRowJT2).Formula = "=CONCATENATE(D2,E2)"
    End If
End With

score 0 · Answer 2 · answered Oct 12 '22 at 09:52

0

Answer, as seen in comments section

lastRowJT2 = Range("A1").End(xlDown).Row

Sheets("PlanningSystemData").Range("A2:A" & lastRowJT2).Formula = "=CONCATENATE(D2,E2)"

answered Oct 12 '22 at 09:52

EuanM28

258
3
14

As @FunThomas correctly mentioned, qualify your objects and use `xlUp` instead of `xlDown`. The link that I gave above talks about that... – Siddharth Rout Oct 12 '22 at 10:31

VBA Range from a specific row to the full column

2 Answers2