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?

Hi,

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

--
Cheers,
Shane Devenshire


"MSteckbeck" wrote:

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?

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


On Tue, 5 Feb 2008 11:14:01 -0800, MSteckbeck
wrote:

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?