Macro Help

N

Nikki

I have a sheet with about 200 rows. That said, I would like to calculate at
the data stop point instead of specifically at row 200 (may change for my
other sheets - 1 sheet may have 500 rows another may have 100 rows).
 
G

Gary''s Student

The last row in the sheet is usually the last row in UsedRange. This is how
the dimensions of UsedRange are calculated:

Sub range_reporter()
Dim r As Range
Dim s As String
Set r = ActiveSheet.UsedRange

nLastRow = r.Rows.Count + r.Row - 1
MsgBox ("last row " & nLastRow)

nLastColumn = r.Columns.Count + r.Column - 1
MsgBox ("last column " & nLastColumn)

nFirstRow = r.Row
MsgBox ("first row " & nFirstRow)

nFirstColumn = r.Column
MsgBox ("first column " & nFirstColumn)

numrow = r.Rows.Count
MsgBox ("number of rows " & numrow)

numcol = r.Columns.Count
MsgBox ("number of columns " & numcol)

s = r.Address
MsgBox ("address " & s)

s = r(1).Address
MsgBox ("address of first cell " & s)
MsgBox ("worksheet " & r.Worksheet.Name)

MsgBox ("workbook " & r.Worksheet.Parent.Name)

MsgBox ("item count " & r.Count)
End Sub
 
V

vavasoo

say your list begins at row 2 and ends at row201. therefore if you like to
calculate for example the total for coulmn D go to cell D1 to write
=sum(D2:D50000). alternatively you can have the formula in cell D50000 and
hide empty rows and insert rows as and when required. if you are not happy
with those 2 tricks you may have to use VBA.
 
N

Nikki

What code would I use to calculate a2*a3 and copy that formula down to the
last row?
 
Top