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
--
Gary''s Student - gsnu200909


"Nikki" wrote:

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).

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.

"Nikki" wrote:

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).

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

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200909


"Nikki" wrote:

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).

What cell are we starting from?
--
Gary''s Student - gsnu200909


"Nikki" wrote:

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

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200909


"Nikki" wrote:

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).

z2 down to the last row of data.

"Gary''s Student" wrote:

What cell are we starting from?
--
Gary''s Student - gsnu200909


"Nikki" wrote:

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

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200909


"Nikki" wrote:

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).

w2*z2 formula copied down to the last row of data.

"Nikki" wrote:

z2 down to the last row of data.

"Gary''s Student" wrote:

What cell are we starting from?
--
Gary''s Student - gsnu200909


"Nikki" wrote:

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

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200909


"Nikki" wrote:

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).