0

I am brand new to VBA. I am seeking an efficient way to combine B and column C into 3rd column A for all populated rows via a macro. The sheet and code I have is as follows:

enter image description here

Sub Macro1()
Worksheets("Sheet1").Range("A1").EntireColumn.Insert
ActiveSheet.Range("A2").Value = "=D2&E2"
ActiveSheet.Range("A3").Value = "=D3&E3"
ActiveSheet.Range("A4").Value = "=D4&E4"
End Sub

I know the code above will technically work, but what about if I have 1000+ lines of data? There's got to be a more efficient way to do this? Thank you so much for any help.

braX
  • 11,506
  • 5
  • 20
  • 33
Davemac737
  • 13
  • 3

1 Answers1

2

You can do this:

Dim ws As WorkSheet

Set ws = ActiveSheet
'using col D to find the last-occupied cell 
With ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "D").End(xlUp).Row)
    .Formula = "=D2 & E2"
    .Value = .Value 'if you want to convert the formulas to values
End With

Excel will auto-adjust the formula for each row

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Thank you so much, Tim!!! You rock. Is it possible to get the formula to stop based on how many rows have data? Like, "continue as long as rows are populated?" That's more like icing on the cake. – Davemac737 Feb 25 '22 at 18:06
  • 2
    @Davemac737 - [find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba)? – BigBen Feb 25 '22 at 18:10
  • See edits above. – Tim Williams Feb 25 '22 at 18:34