VBA: Put header text before cell data

[azov5]

VBA: I have multi column data with header.
I want to add the header text before text in every cell data in respective column.

 

[lrobbo314]

Something like this. This code assumes your data begins in cell A1. You may need to adjust how the range is set depending on what your data looks like.

Sub mk()
Dim r As Range: Set r = Range("A1").CurrentRegion
Dim ar() As Variant: ar = Application.Transpose(r.Value)


For co = 1 To UBound(ar)
    For ro = 2 To UBound(ar, 2)
        ar(co, ro) = ar(co, 1) & ar(co, ro)
    Next ro
Next co


r.Value = Application.Transpose(ar)


End Sub

 

[Fluff]

Similar, but without the Transpose

Sub azov5()
    Dim Ary As Variant
    Dim r As Long, c As Long

    Ary = Range("A1").CurrentRegion.Value2
    For r = 2 To UBound(Ary)
        For c = 1 To UBound(Ary, 2)
            Ary(r, c) = Ary(1, c) & " " & Ary(r, c)
        Next c
    Next r
    Range("A1").CurrentRegion.Value2 = Ary
End Sub

This will also add a space between the data & the header

 

[azov5]

lrobbo314 here is the problem
Run time error 7


out of memory
Yellow color part: : ar = Application.Transpose(r.Value)

 

[azov5]

Fluff

Run time error 1004
Application-defined or object defined error


"Range("A1").CurrentRegion.Value2 = Ary"

Its not working on all rows

 

[Fluff]

Are your headers in row 1 starting in col A?
Also do you have any blank rows in you data?

 

[azov5]

Are your headers in row 1 starting in col A?
Yes !
Also do you have any blank rows in you data?
Yes !

 

[lrobbo314]

What range is your data in Column A through Column ...?

 

[azov5]

A1: Bw34218

 

[lrobbo314]

See if this works.

Sub mk()
Dim r As Range: Set r = Range("A1:BW" & Range("A" & Rows.Count).End(xlUp).Row)
Dim ar() As Variant: ar = Application.Transpose(r.Value)


For co = 1 To UBound(ar)
    For ro = 2 To UBound(ar, 2)
        ar(co, ro) = ar(co, 1) & ar(co, ro)
    Next ro
Next co


r.Value = Application.Transpose(ar)
End Sub