Add column headers in a worksheet using VBA

[AussieSteve]

Hi guys.
Im just starting out with VBA in Excel, and have a question like millions before me, anyway it goes like this. I want to add my own column header names using VBA , and have used running a macro with success, but now want to shorten the code to a few lines of code rather then about 20 + that it now stands at. This is what the macro looks like at the start, I am in the process of learning methods, syntax, objects etc and it's a lot to take in and know exactly what to look for to solve even smallest prob like mine. Any help would be appreciated to move me on in my VBA journey.
Thanks.

ActiveCell.FormulaR1C1 = "dogs"
Range("C1").Select
ActiveCell.FormulaR1C1 = "cats"
Range("D1").Select
ActiveCell.FormulaR1C1 = "birds"

 

[Michael M]

Something like

sub MM1
Range("C1").value= "dogs"
 Range("D1").value = "cats"
 Range("E1").Value = "birds" 
end sub

 

[vcoolio]

Hello AussieSteve,

Another option:

One way to do this would be to create a list in another column (or even in another worksheet) of the column headings that you wish to use. Lets assume that you've created a list on the same worksheet but over in Column S.

A code as follows would transpose the list starting in cell A1:-

Sub Test()

Dim lr As Long: lr = Range("S" & Rows.Count).End(xlUp).Row

Range("S1:S" & lr).Copy
Range("A1").PasteSpecial , Transpose:=True

Application.CutCopyMode = False

End Sub

It should help to get you started.
Having a list will allow you to add/remove/alter the headings as you please. Just run the code as you make changes.

Cheerio,
vcoolio.

 

[My Aswer Is This]

Try this:

Your sample started with column B
My script starts in column A and goes to column 3

Modify the script to your needs.

You should be able to change 3 to 10 or what ever
And add more names.

Sub Dogs_And_Cats()
'Modified  11/26/2018  6:58:08 AM  EST
Application.ScreenUpdating = False
Dim i As Long
    For i = 1 To 3
        With Cells(1, i)
            Select Case .Column
                Case i
                    .Value = Choose(i, "Dogs", "Cats", "Birds")
            End Select
        End With
    Next
Application.ScreenUpdating = True
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
Another option

Sub Headers()
   Range("B1:E1").Value = Array("Dogs", "Cats", "Birds", "Insects")
End Sub

 

[My Aswer Is This]

There are several other ways this could be done also. Like why would you need a script to put in the names you want in your header row.

Do they all change for some logical reason?

What event would happen for you to want them to change.

Like do the names in each cell in row 1 represent a sheet name or what.
Normally we only need scripts to do things like this when some event changes from time to time and we want a script to run to change things.

Like this script could add the sheet name to each cell in row(1)

Sub Sheet_Names_To_Row_One()
'Modified 11/26/2018 9:26:48 AM  EST
Application.ScreenUpdating = False
Dim i As Long
For i = 1 To Sheets.Count
    Cells(1, i).Value = Sheets(i).Name
Next
Application.ScreenUpdating = True
End Sub

 

[AussieSteve]

Works perfectly.your code is the shortest.it will what I will try to build into my code going forward.
thanks again.

 

[Rick Rothstein]

Hi & welcome to MrExcel.
Another option

Sub Headers()
   Range("B1:E1").Value = Array("Dogs", "Cats", "Birds", "Insects")
End Sub

Why such a long macro?

Sub Headers()
   [B1:E1] = [{"Dogs","Cats","Birds","Insects"}]
End Sub

Edit Note: Actually, even that is too long

Sub Headers()
   [B1:E1] = Split("Dogs Cats Birds Insects")
End Sub

 

[AussieSteve]

Thank you Rick and everyone else that contributed to my question. It seems I should started this coding game a long time ago.

 

[Fluff]

Glad we could help & thanks for the feedback