VBA ignoring last row

[JoeRooney]

Hi,

I have the below code which joins cells from B:BW with a "," delimiter for each row, it is working perfect except it skips the last row.

There is data in column B in the last row so I cant figure out why it would skip it.

Would anyone have any idea why it skips it?

Dim i As Long
Dim LastRow As Long
LastRow = Range("B" & Rows.Count).End(xlUp).Row

    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "TOM Policy"

    For i = 2 To LastRow
        Range("A" & i).Formula = Join(Application.Index(Range("B" & i & ":BW" & i).Value, 1, 0), ",")
    Next i

 

[footoo]

You need to assign a value to LastRow after inserting the row, not before inserting.

 

[JoeRooney]

Thank you footoo , it is working as expected now.

 

[footoo]

You might want to try this alternative code :

Dim i As Long, LastRow As Long
Columns(1).Insert
Rows(1).Insert
Range("A1") = "TOM Policy"
Range("A2:A" & Cells(Rows.Count, "C").End(xlUp).Row).Formula = _
    Join(Application.Index(Range("B2:BW2").Value, 1, 0), ",")