Macro Help
 

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

Macro Help
 

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

Macro Help
 

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

Macro Help
 

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

Macro Help
 

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

Macro Help
 

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

Macro Help
 

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