Macro Help

  • Thread starter Thread starter Nikki
  • Start date Start date
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).
 
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
 
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.
 
Back
Top