Concatenate with text and punctuation dynamic range

Question

I'm trying to add punctuation and text by using concatenate to a range of dynamic cells in excel and I'm trying to figure out the VBA code for it.

So, I'm doing the following:

Final result:

With macro recorder I get this

Range("B3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=""<>""&RC[-1]"
Range("B3").Select
Selection.AutoFill Destination:=Range("B3:B5")
Range("B3:B5").Select

Is there a way that I can do the autofill to be dynamic, it's currently focused on cells B3 to B5 (as I will have many more cells and never a fixed amount of cells)?

Is there an alternative concatenate formula that I can use?

Many Thanks

You can [find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). — BigBen, Apr 04 '22 at 13:28

VBasic2008 · Accepted Answer · 2022-04-04T14:17:59.390

Fill Down a Simple Formula Using VBA

Option Explicit

Sub Test1()
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    With ws.Range("A3:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
        .EntireRow.Columns("B").Formula = "=""<>""&" & .Cells(1).Address(0, 0)
        ' or
        '.Offset(, 1).Formula = "=""<>""&" & .Cells(1).Address(0, 0)
    End With
    
End Sub


Sub Test2()
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    With ws.Range("B3:B" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
        .Formula = "=""<>""&" & .Cells(1).EntireRow.Columns("A").Address(0, 0)
        ' Or
        '.Formula = "=""<>""&" & .Cells(1).Offset(, -1).Address(0, 0)
    End With
    
End Sub

Hi, I used the very first piece of code and it worked perfectly. Thank you! — Bikat Uprety, Apr 04 '22 at 14:40

Concatenate with text and punctuation dynamic range

1 Answers1

Fill Down a Simple Formula Using VBA