VBA Range from a specific row to the full column-CodePudding

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!

CodePudding user response：

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

CodePudding user response：

Answer, as seen in comments section

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

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