Formatting/Totalling Macro with varying rows of data
 

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?

Formatting/Totalling Macro with varying rows of 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?

Formatting/Totalling Macro with varying rows of 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?