Formatting/Totalling Macro with varying rows of data

[MSteckbeck]

Fairly new to VBA, so I'm struggling with how to write a formatting macro for
an excel file. The file is automatically generated by Access with each report
run, but each report run could have a varying number of rows. How do I write
the formatting macro so that I can both format the columns/rows as needed and
put in the sum at the bottom of each data?

 

[ShaneDevenshire]

Hi,

Why don't you show us your code. Also, are the only thing that changes the
number of rows?

 

[Gord Dibben]

This macro will place a SUM formula at the bottom of each column.

Number of rows and columns can be variable but assumes each column has same
number of rows or at least Column A is always longest column.

Sub Sumup22()
Dim rng As Range
Dim rng2 As Long
Dim rng1 As Range
Range("A1").Select
rng2 = Range("A1", Cells(ActiveCell.Row, Columns.Count) _
..End(xlToLeft)).Columns.Count
Set rng = Range("A1", Range("A" & Rows.Count). _
End(xlUp).Address)
Set rng1 = rng.Offset(rng.Rows.Count, 0).Resize(1, rng2)
rng1.Formula = Application.ConvertFormula _
("=Sum(" & rng.Address & ")", xlA1, xlA1, xlRelative)
End Sub

The formatting part will have be done by you unless you can specify what you
need.


Gord Dibben MS Excel MVP