Excel VBA, lastCol from Row2 to lastRow and concatenate formula-CodePudding

There's 2 thing I am stuck with

I am trying to apply a formula to the last Column from Row 2 to the last row
Formula: concat rows based on another sheet

Option Explicit

Sub concat()

Dim lastCol As Long
Dim lastRow As Long

lastRow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
With Worksheets("Sheet1")
    lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column   1
End With

With Worksheets("Sheet1")
    Range(Cells(2, lastCol) & lastRow) = Application.WorksheetFunction.concat(application.WorksheetFunction.Index(cells(2,match(worksheets("Sheet2").Range("K3").value,worksheets("Sheet1").rows(1)), application.WorksheetFunction.Index(cells(2,match(worksheets("Sheet2").Range("L3").value,worksheets("Sheet1").rows(1)))
End With

End Sub

CodePudding user response：

Just use this code and replace with your formula

Option Explicit

Sub concat()
Dim aRng As Range
Dim lastCol As Long
Dim lastRow As Long

Set aRng = Worksheets("Sheet1").UsedRange
lastRow = aRng.Cells(1, 1).Row   aRng.Rows.Count - 1
lastCol = aRng.Cells(1, 1).Column   aRng.Columns.Count - 1

With Worksheets("Sheet1")
    'Write the formula in cell(2,lastCol), just below Headers
    .Cells(2, lastCol).Formula = "=F2   65"  'put your formula in the brackets
    
    'now autofill to end (or copy/paste formula)
    .Cells(2, lastCol).AutoFill Destination:=.Range(Cells(2, lastCol), Cells(lastRow, lastCol)), Type:=xlFillDefault
End With
End Sub