VBA to format excel columns

  • Thread starter Thread starter Pendragon
  • Start date Start date
P

Pendragon

Access03/WinXP

I have set up an export to Excel and have done some formatting thanks to
some great help from other threads. I understand that I could set a specific
column width using .Range(x,y).ColumnWidth = SomeNumber.

Is there a way to use the Autofit feature in Excel from VBA?

Thanks.
 
Yes, here are some standard formatting that I do with almost all of my exports.

'1 = black
'2 = white
'3 = red
'5 = blue
'10 = green
'13 = purple

'Format the headers
objXLSheet6.Range("A1:R1").Font.Bold = True
objXLSheet6.Range("A1:R1").HorizontalAlignment = xlCenter
objXLSheet6.Range("A1:R1").Font.ColorIndex = 2
objXLSheet6.Range("A1:R1").Interior.ColorIndex = 1

'AutoFit the columns
objXLSheet6.Range("A:R").Columns.AutoFit

'Freeze Panes
objXLSheet6.Activate
objXLSheet6.Range("2:2").Select
objXLApp.ActiveWindow.FreezePanes = True

'Set the cursor back on the first cell
objXLSheet6.Range("A1:A1").Select
 
Fantastic!!

One question - my columns will be variable in number. How do you set that
instead of "A:R"? "A:___"

Thank you so much.
 
Back
Top