Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


Automating Excel Formatting

How to Automate Formatting Using VBA in Excel

Rather than manually setting column widths, Zoom percentage or currency formats on an Excel sheet, a macro can save time and effort.

Automating Column Width in Excel

Columns in an Excel spreadsheet can be automated in code to set width, format, font, etc. "on the fly" for your automated reports.

Excel Data

Column Width:
This code in a VBA Macro will set the width of each of the columns.

Public Sub ColumnSettings()
'set widths
   Columns("A:A").Select
   Selection.ColumnWidth = 13
   Columns("B:B").Select
   Selection.ColumnWidth = 27
   Columns("C:C").Select
   Selection.ColumnWidth = 29
   Columns("D:D").Select
   Selection.ColumnWidth = 35
   Columns("E:E").Select
   Selection.ColumnWidth = 8.5
   Columns("F:F").Select
   Selection.ColumnWidth = 20
   Columns("G:G").Select
   Selection.ColumnWidth = 13
   Columns("H:H").Select
   Selection.ColumnWidth = 22
End Sub

Automating Excel Zoom Property Setting

This code will automatically set the zoom setting for the worksheet(s).

Public Sub ZoomSetting()
   'select sheet and set zoom
  Sheets("Data").Select
  ActiveWindow.Zoom = 80
End Sub

Automate Excel Freeze Top Row

Freeze the top row so that the data can be scrolled through.

Public Sub TopRow()
   'freeze the top row
   Rows("1:2").Select
   Range("A2").Activate
   ActiveWindow.FreezePanes = True
End Sub

Setting Excel Currency Format Setting in Code

To set the format for the Sales YTD column.

Public Sub ColCurrFormat()
   Columns("G:G").Select
   Selection.NumberFormat = "$#,##0.00"
End Sub

Put the various procedures into the ThisWorkbook code:

Public Sub GenRept()
   delSheets
   PopWksht
   DynamicHeader
   ColumnSettings
   ZoomSetting
   TopRow
   ColCurrFormat
End Sub

And when you run the macro, the perfectly formatted data will automatically be set.

Excel Format After

Automating the formatting makes report creation much faster and easier.