Macro to Add Prefix or Suffix to Column

[bohsi]

Hi,

I recorded the following macro to add a thorn symbol before and after each record in a table. I can't figure out how to edit the macro so that it will work with a different set a data where there is a different amount of records. Help is much appreciated. Thanks.
Sub Macro6()

Range("A1:A26").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$A$26"), , xlNo).Name = _
"Table9"
Range("Table9[#All,[Column1]]").Select
ActiveSheet.ListObjects("Table9").TableStyle = "TableStyleLight1"
Range("B1").Select
ActiveCell.FormulaR1C1 = "þ"
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=Table9[#Headers,[þ]]&[@Column1]&Table9[#Headers,[þ]]"
Range("C3").Select

End Sub>

 

[tusharm]

I am not sure what you want to do but the below tested code duplicates your functionality while working with any number of contiguous cell in col. A (starting with A1).

Sub Macro6()
    Dim aLO As ListObject
    Set aLO = ActiveSheet.ListObjects.Add(xlSrcRange, _
        Range(Range("a1"), Range("a1").End(xlDown)), , xlNo)
    aLO.TableStyle = "TableStyleLight1"
    Range("B1").FormulaR1C1 = "þ"
    Range("C2").FormulaR1C1 = _
        "=" & aLO.Name & "[[#Headers],[þ]]&[@Column1]&" _
            & aLO.Name & "[[#Headers],[þ]]"
    End Sub

Hi,

I recorded the following macro to add a thorn symbol before and after each record in a table. I can't figure out how to edit the macro so that it will work with a different set a data where there is a different amount of records. Help is much appreciated. Thanks.
Sub Macro6()

Range("A1:A26").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$A$26"), , xlNo).Name = _
"Table9"
Range("Table9[#All,[Column1]]").Select
ActiveSheet.ListObjects("Table9").TableStyle = "TableStyleLight1"
Range("B1").Select
ActiveCell.FormulaR1C1 = "þ"
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=Table9[#Headers,[þ]]&[@Column1]&Table9[#Headers,[þ]]"
Range("C3").Select

End Sub>