Hi,

I am trying to set up a macro to fill up formulas two rows below the last
row of a worksheet. The problem is this number of rows is different each
week. I have a hard time making this macro works for different week. Thanks.

Below is the code:

Sub Macro13()
'
ActiveCell.FormulaR1C1 = "Total"
Range("M204").Select
ActiveWindow.SmallScoll Down:=12
Range("M199").Select
Selection.Copy
Range("M204").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUM(RC[1])-SUM(RC[2])"
Range("M205").Select
ActiveWindow.SmallScroll ToRight:=1
Range("N204").Select
ActiveCell.FormulaR1C1 = "=+R[-2]C/R[-203]C"
Range("N204").Select
Selection.AutoFill Destination:=Range("N204:S204"), Type:=xlFillDefault
Range("N204:S204").Select
Selection.NumberFormat = "#,##0.0_);[Red](#,##0.0)"
Selection.Font.Bold = True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Range("M204").Select
End Sub

The last row that was ever "touched" can be found using
activecell.SpecialCells(xlCellTypeLastCell).Row

however, sometimes cleared contents from cells are still treated as "used"
rows until the file is re-saved.

This gives you the real last row with data:
activesheet.usedrange.SpecialCells(xlCellTypeLastC ell).Row

HTH

Bill Benson
http://www.xlcreations.com

"JN" wrote in message
...
Hi,

I am trying to set up a macro to fill up formulas two rows below the last
row of a worksheet. The problem is this number of rows is different each
week. I have a hard time making this macro works for different week.
Thanks.

Below is the code:

Sub Macro13()
'
ActiveCell.FormulaR1C1 = "Total"
Range("M204").Select
ActiveWindow.SmallScoll Down:=12
Range("M199").Select
Selection.Copy
Range("M204").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUM(RC[1])-SUM(RC[2])"
Range("M205").Select
ActiveWindow.SmallScroll ToRight:=1
Range("N204").Select
ActiveCell.FormulaR1C1 = "=+R[-2]C/R[-203]C"
Range("N204").Select
Selection.AutoFill Destination:=Range("N204:S204"), Type:=xlFillDefault
Range("N204:S204").Select
Selection.NumberFormat = "#,##0.0_);[Red](#,##0.0)"
Selection.Font.Bold = True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Range("M204").Select
End Sub

Hi Bill,

This gives you the real last row with data:
activesheet.usedrange.SpecialCells(xlCellTypeLastC ell).Row


From the Intermediate window:

ActiveWorkbook.Sheets.Add
ActiveSheet.Range("A2000")=100
activesheet.Range("A2000").Font.Bold = True

?ActiveSheet.UsedRange. _
SpecialCells(xlCellTypeLastCell).Row
2000

ActiveSheet.Range("A2000").ClearContents

?isempty(ActiveSheet.Range("A2000"))
True

?ActiveSheet.UsedRange. _
SpecialCells(xlCellTypeLastCell).Row
2000

---
Regards,
Norman



"William Benson" wrote in message
...
The last row that was ever "touched" can be found using
activecell.SpecialCells(xlCellTypeLastCell).Row

however, sometimes cleared contents from cells are still treated as "used"
rows until the file is re-saved.

This gives you the real last row with data:
activesheet.usedrange.SpecialCells(xlCellTypeLastC ell).Row

HTH

Bill Benson
http://www.xlcreations.com

xlCellTypeLastCell just requires a range to anchor to. After that, the
results is independent of what range you used (other than what sheet it is
on).

UsedRange and xlCellTypeLastCell return the same interpretation of the last
used cell - which from Excel's perspective is correct - it represents the
last cell Excel is maintaining detailed information about.
--
Regards,
Tom Ogilvy


"William Benson" wrote in message
...
The last row that was ever "touched" can be found using
activecell.SpecialCells(xlCellTypeLastCell).Row

however, sometimes cleared contents from cells are still treated as "used"
rows until the file is re-saved.

This gives you the real last row with data:
activesheet.usedrange.SpecialCells(xlCellTypeLastC ell).Row

HTH

Bill Benson
http://www.xlcreations.com

"JN" wrote in message
...
Hi,

I am trying to set up a macro to fill up formulas two rows below the
last
row of a worksheet. The problem is this number of rows is different each
week. I have a hard time making this macro works for different week.
Thanks.

Below is the code:

Sub Macro13()
'
ActiveCell.FormulaR1C1 = "Total"
Range("M204").Select
ActiveWindow.SmallScoll Down:=12
Range("M199").Select
Selection.Copy
Range("M204").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUM(RC[1])-SUM(RC[2])"
Range("M205").Select
ActiveWindow.SmallScroll ToRight:=1
Range("N204").Select
ActiveCell.FormulaR1C1 = "=+R[-2]C/R[-203]C"
Range("N204").Select
Selection.AutoFill Destination:=Range("N204:S204"),
Type:=xlFillDefault
Range("N204:S204").Select
Selection.NumberFormat = "#,##0.0_);[Red](#,##0.0)"
Selection.Font.Bold = True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Range("M204").Select
End Sub

Does it mean by replacing Range("M204") with
"activecell.SpecialCells(xlCellTypeLastCell).R ow" will work? Could you tell
me where I should put this code in? I am a beginner in this. Can I apply the
same code for other specific cell, like N204, in the macro? Thanx.



"William Benson" wrote:

The last row that was ever "touched" can be found using
activecell.SpecialCells(xlCellTypeLastCell).Row

however, sometimes cleared contents from cells are still treated as "used"
rows until the file is re-saved.

This gives you the real last row with data:
activesheet.usedrange.SpecialCells(xlCellTypeLastC ell).Row

HTH

Bill Benson
http://www.xlcreations.com

"JN" wrote in message
...
Hi,

I am trying to set up a macro to fill up formulas two rows below the last
row of a worksheet. The problem is this number of rows is different each
week. I have a hard time making this macro works for different week.
Thanks.

Below is the code:

Sub Macro13()
'
ActiveCell.FormulaR1C1 = "Total"
Range("M204").Select
ActiveWindow.SmallScoll Down:=12
Range("M199").Select
Selection.Copy
Range("M204").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUM(RC[1])-SUM(RC[2])"
Range("M205").Select
ActiveWindow.SmallScroll ToRight:=1
Range("N204").Select
ActiveCell.FormulaR1C1 = "=+R[-2]C/R[-203]C"
Range("N204").Select
Selection.AutoFill Destination:=Range("N204:S204"), Type:=xlFillDefault
Range("N204:S204").Select
Selection.NumberFormat = "#,##0.0_);[Red](#,##0.0)"
Selection.Font.Bold = True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Range("M204").Select
End Sub

Here's what I had tested:

(1) Insert a new worksheet
(2) Put letters in cells A1, A2, and A25
(3) Click in Cell A1
(4a) in Immediate Window:
?Activecell.SpecialCells(xlCellTypeLastCell).Row

Result is 25

(4b) in Immediate Window:
?ActiveSheet.UsedRange.SpecialCells(xlCellTypeLast Cell).Row

Result is 25

(5) Clear contents of cell A25
(6) Click on A1
(7a) in Immediate Window: ?Activecell.SpecialCells(xlCellTypeLastCell).Row

Result is 25 (STILL!!!)

(7b) in Immediate Window:
?ActiveSheet.UsedRange.SpecialCells(xlCellTypeLast Cell).Row

Result is 2 (NOT 25!!!)

I cannot explain your results but I can tell you that I have tested mine!

Bill





"Norman Jones" wrote in message
...
Hi Bill,

This gives you the real last row with data:
activesheet.usedrange.SpecialCells(xlCellTypeLastC ell).Row


From the Intermediate window:

ActiveWorkbook.Sheets.Add
ActiveSheet.Range("A2000")=100
activesheet.Range("A2000").Font.Bold = True

?ActiveSheet.UsedRange. _
SpecialCells(xlCellTypeLastCell).Row
2000

ActiveSheet.Range("A2000").ClearContents

?isempty(ActiveSheet.Range("A2000"))
True

?ActiveSheet.UsedRange. _
SpecialCells(xlCellTypeLastCell).Row
2000

---
Regards,
Norman



"William Benson" wrote in message
...
The last row that was ever "touched" can be found using
activecell.SpecialCells(xlCellTypeLastCell).Row

however, sometimes cleared contents from cells are still treated as
"used" rows until the file is re-saved.

This gives you the real last row with data:
activesheet.usedrange.SpecialCells(xlCellTypeLastC ell).Row

HTH

Bill Benson
http://www.xlcreations.com

Hi Bill,

I cannot explain your results but I can tell you that I have tested mine!

Since my observations were copied direct from the Intermediate window, I
will leave it to you to judge if I tested my results


I cannot explain your results

Perhaps the salient clue (and an underlying purpose of my post) is in my
Imtermediate window line:

activesheet.Range("A2000").Font.Bold = True

More generally, clearing a cells contents does just that - the contents, not
any special formatting. Formatted cells will show up in your UsedRange
technique even without associated data,

Try your tests again, adding some special formatting.

---
Regards,
Norman



"William Benson" wrote in message
...
Here's what I had tested:

(1) Insert a new worksheet
(2) Put letters in cells A1, A2, and A25
(3) Click in Cell A1
(4a) in Immediate Window:
?Activecell.SpecialCells(xlCellTypeLastCell).Row

Result is 25

(4b) in Immediate Window:
?ActiveSheet.UsedRange.SpecialCells(xlCellTypeLast Cell).Row

Result is 25

(5) Clear contents of cell A25
(6) Click on A1
(7a) in Immediate Window:
?Activecell.SpecialCells(xlCellTypeLastCell).Row

Result is 25 (STILL!!!)

(7b) in Immediate Window:
?ActiveSheet.UsedRange.SpecialCells(xlCellTypeLast Cell).Row

Result is 2 (NOT 25!!!)

I cannot explain your results but I can tell you that I have tested mine!

Bill

Norman,

I see, thanks for the correction!

Can you help the OP then since I cannot?

B.

Tom, I do not think that the result from using xlCellTypeLastCell as an
argument is completely independent of the range based upon. If you look at
the test I recorded for Norman, it is pretty clear that different results
are obtained depending on whether I am querying
Activecell.SpecialCells(xlCellTypeLastCell).Row or
Activesheet.Usedrange.SpecialCells(xlCellTypeLastC ell).Row

Norman's point was 100% valid -- that a non-empty cell can still be dirty
because its format has been changed -- and for this reason my premise that
either of the two methods above would give the last row where data is found
is invalid ... but neither can I agree with the point you made (forgive my
insolence).

As with most situations in this forum, I am likely led astray by my inferior
Excel instincts ...
but I will risk modest embarassment it in the pursuit of the Higher Truth
:-)

Thanks much.


"Tom Ogilvy" wrote in message
...
xlCellTypeLastCell just requires a range to anchor to. After that, the
results is independent of what range you used (other than what sheet it is
on).

UsedRange and xlCellTypeLastCell return the same interpretation of the
last
used cell - which from Excel's perspective is correct - it represents the
last cell Excel is maintaining detailed information about.
--
Regards,
Tom Ogilvy


"William Benson" wrote in message
...
The last row that was ever "touched" can be found using
activecell.SpecialCells(xlCellTypeLastCell).Row

however, sometimes cleared contents from cells are still treated as
"used"
rows until the file is re-saved.

This gives you the real last row with data:
activesheet.usedrange.SpecialCells(xlCellTypeLastC ell).Row

HTH

Bill Benson
http://www.xlcreations.com

"JN" wrote in message
...
Hi,

I am trying to set up a macro to fill up formulas two rows below the
last
row of a worksheet. The problem is this number of rows is different
each
week. I have a hard time making this macro works for different week.
Thanks.

Below is the code:

Sub Macro13()
'
ActiveCell.FormulaR1C1 = "Total"
Range("M204").Select
ActiveWindow.SmallScoll Down:=12
Range("M199").Select
Selection.Copy
Range("M204").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUM(RC[1])-SUM(RC[2])"
Range("M205").Select
ActiveWindow.SmallScroll ToRight:=1
Range("N204").Select
ActiveCell.FormulaR1C1 = "=+R[-2]C/R[-203]C"
Range("N204").Select
Selection.AutoFill Destination:=Range("N204:S204"),
Type:=xlFillDefault
Range("N204:S204").Select
Selection.NumberFormat = "#,##0.0_);[Red](#,##0.0)"
Selection.Font.Bold = True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Range("M204").Select
End Sub

Hi JN,

I am trying to set up a macro to fill up formulas two rows below the last
row of a worksheet. The problem is this number of rows is different each
week. I have a hard time making this macro works for different week.


To return the last data row in a worksheet, you could use the following
function posted by Ron de Bruin:

'=================
Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
'<<=================

In your code you could use it like:

Dim Lrow as long

Lrow = LastRow(ActiveSheet)


---
Regards,
Norman



"JN" wrote in message
...
Hi,

I am trying to set up a macro to fill up formulas two rows below the last
row of a worksheet. The problem is this number of rows is different each
week. I have a hard time making this macro works for different week.
Thanks.

Below is the code:

Sub Macro13()
'
ActiveCell.FormulaR1C1 = "Total"
Range("M204").Select
ActiveWindow.SmallScoll Down:=12
Range("M199").Select
Selection.Copy
Range("M204").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUM(RC[1])-SUM(RC[2])"
Range("M205").Select
ActiveWindow.SmallScroll ToRight:=1
Range("N204").Select
ActiveCell.FormulaR1C1 = "=+R[-2]C/R[-203]C"
Range("N204").Select
Selection.AutoFill Destination:=Range("N204:S204"), Type:=xlFillDefault
Range("N204:S204").Select
Selection.NumberFormat = "#,##0.0_);[Red](#,##0.0)"
Selection.Font.Bold = True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Range("M204").Select
End Sub

If I want to reset the location of xlCellTypeLastCell and the definition of
UsedRange as well, I simply use the command

ActiveSheet.UsedRange

If you get a different answer in your tests, then I would suggest that you
have reset the usedrange, by including Activesheet.Usedrange in the sequece
within your command.

What I stated is correct and doesn't disagree with what you stated.

In your test, before using Activecell use the single command
Activesheet.UsedRange

I predict you will get the same answer which is what I said.

Also, resetting the definition of Usedrange has been progressive in
different versions of excel So what may be "truth" in one version may not
be "truth" in another version. In excel 95, the file had to be saved and
exited. In excel 97, saving was sufficient and so forth.


--
regards,
Tom Ogilvy

"William Benson" wrote in message
...
Tom, I do not think that the result from using xlCellTypeLastCell as an
argument is completely independent of the range based upon. If you look at
the test I recorded for Norman, it is pretty clear that different results
are obtained depending on whether I am querying
Activecell.SpecialCells(xlCellTypeLastCell).Row or
Activesheet.Usedrange.SpecialCells(xlCellTypeLastC ell).Row

Norman's point was 100% valid -- that a non-empty cell can still be dirty
because its format has been changed -- and for this reason my premise that
either of the two methods above would give the last row where data is
found
is invalid ... but neither can I agree with the point you made (forgive my
insolence).

As with most situations in this forum, I am likely led astray by my
inferior
Excel instincts ...
but I will risk modest embarassment it in the pursuit of the Higher Truth
:-)

Thanks much.


"Tom Ogilvy" wrote in message
...
xlCellTypeLastCell just requires a range to anchor to. After that, the
results is independent of what range you used (other than what sheet it
is
on).

UsedRange and xlCellTypeLastCell return the same interpretation of the
last
used cell - which from Excel's perspective is correct - it represents
the
last cell Excel is maintaining detailed information about.
--
Regards,
Tom Ogilvy


"William Benson" wrote in message
...
The last row that was ever "touched" can be found using
activecell.SpecialCells(xlCellTypeLastCell).Row

however, sometimes cleared contents from cells are still treated as
"used"
rows until the file is re-saved.

This gives you the real last row with data:
activesheet.usedrange.SpecialCells(xlCellTypeLastC ell).Row

HTH

Bill Benson
http://www.xlcreations.com

"JN" wrote in message
...
Hi,

I am trying to set up a macro to fill up formulas two rows below the
last
row of a worksheet. The problem is this number of rows is different
each
week. I have a hard time making this macro works for different week.
Thanks.

Below is the code:

Sub Macro13()
'
ActiveCell.FormulaR1C1 = "Total"
Range("M204").Select
ActiveWindow.SmallScoll Down:=12
Range("M199").Select
Selection.Copy
Range("M204").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUM(RC[1])-SUM(RC[2])"
Range("M205").Select
ActiveWindow.SmallScroll ToRight:=1
Range("N204").Select
ActiveCell.FormulaR1C1 = "=+R[-2]C/R[-203]C"
Range("N204").Select
Selection.AutoFill Destination:=Range("N204:S204"),
Type:=xlFillDefault
Range("N204:S204").Select
Selection.NumberFormat = "#,##0.0_);[Red](#,##0.0)"
Selection.Font.Bold = True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Range("M204").Select
End Sub

From your code, I would suggest something like this. This assumes all
columns have data down to the same row so the formula will all be on the
same row (appears to be consistent with your recorded code).

Sub BuildFormulas()
Set rng = Cells(Rows.Count, "M").End(xlUp)
Set rng = rng.Offset(2, 0)
With rng.Resize(1, 7) ' M to S
.FormulaR1C1 = "=R[-2]C/R1C"
.NumberFormat = "#,##0.0_);[Red](#,##0.0)"
.Font.Bold = True
End With
ActiveSheet.Outline.ShowLevels RowLevels:=2
End Sub

In my test, if the data ended in Row 202, then the formula in M204 was

=M202/M$1

Similar formulas were entered in N202 to S202

If this isn't the correct formula, you would need to adjust the formula
portion.

--
Regards,
Tom Ogilvy




"JN" wrote in message
...
Does it mean by replacing Range("M204") with
"activecell.SpecialCells(xlCellTypeLastCell).R ow" will work? Could you
tell
me where I should put this code in? I am a beginner in this. Can I apply
the
same code for other specific cell, like N204, in the macro? Thanx.



"William Benson" wrote:

The last row that was ever "touched" can be found using
activecell.SpecialCells(xlCellTypeLastCell).Row

however, sometimes cleared contents from cells are still treated as
"used"
rows until the file is re-saved.

This gives you the real last row with data:
activesheet.usedrange.SpecialCells(xlCellTypeLastC ell).Row

HTH

Bill Benson
http://www.xlcreations.com

"JN" wrote in message
...
Hi,

I am trying to set up a macro to fill up formulas two rows below the
last
row of a worksheet. The problem is this number of rows is different
each
week. I have a hard time making this macro works for different week.
Thanks.

Below is the code:

Sub Macro13()
'
ActiveCell.FormulaR1C1 = "Total"
Range("M204").Select
ActiveWindow.SmallScoll Down:=12
Range("M199").Select
Selection.Copy
Range("M204").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUM(RC[1])-SUM(RC[2])"
Range("M205").Select
ActiveWindow.SmallScroll ToRight:=1
Range("N204").Select
ActiveCell.FormulaR1C1 = "=+R[-2]C/R[-203]C"
Range("N204").Select
Selection.AutoFill Destination:=Range("N204:S204"),
Type:=xlFillDefault
Range("N204:S204").Select
Selection.NumberFormat = "#,##0.0_);[Red](#,##0.0)"
Selection.Font.Bold = True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Range("M204").Select
End Sub

Yes, John Walkenbach says to use Activesheet.UsedRange as a trigger. But you
had not said that and I did not know you implied it, sorry.

Someday we will not need to know what Excel 95 did ... but then we'll be
looking back to what Excel 2003 "did".

:-)



"Tom Ogilvy" wrote in message
...
If I want to reset the location of xlCellTypeLastCell and the definition
of
UsedRange as well, I simply use the command

ActiveSheet.UsedRange

If you get a different answer in your tests, then I would suggest that you
have reset the usedrange, by including Activesheet.Usedrange in the
sequece
within your command.

What I stated is correct and doesn't disagree with what you stated.

In your test, before using Activecell use the single command
Activesheet.UsedRange

I predict you will get the same answer which is what I said.

Also, resetting the definition of Usedrange has been progressive in
different versions of excel So what may be "truth" in one version may not
be "truth" in another version. In excel 95, the file had to be saved
and
exited. In excel 97, saving was sufficient and so forth.


--
regards,
Tom Ogilvy

"William Benson" wrote in message
...
Tom, I do not think that the result from using xlCellTypeLastCell as an
argument is completely independent of the range based upon. If you look
at
the test I recorded for Norman, it is pretty clear that different results
are obtained depending on whether I am querying
Activecell.SpecialCells(xlCellTypeLastCell).Row or
Activesheet.Usedrange.SpecialCells(xlCellTypeLastC ell).Row

Norman's point was 100% valid -- that a non-empty cell can still be dirty
because its format has been changed -- and for this reason my premise
that
either of the two methods above would give the last row where data is
found
is invalid ... but neither can I agree with the point you made (forgive
my
insolence).

As with most situations in this forum, I am likely led astray by my
inferior
Excel instincts ...
but I will risk modest embarassment it in the pursuit of the Higher Truth
:-)

Thanks much.


"Tom Ogilvy" wrote in message
...
xlCellTypeLastCell just requires a range to anchor to. After that, the
results is independent of what range you used (other than what sheet it
is
on).

UsedRange and xlCellTypeLastCell return the same interpretation of the
last
used cell - which from Excel's perspective is correct - it represents
the
last cell Excel is maintaining detailed information about.
--
Regards,
Tom Ogilvy


"William Benson" wrote in message
...
The last row that was ever "touched" can be found using
activecell.SpecialCells(xlCellTypeLastCell).Row

however, sometimes cleared contents from cells are still treated as
"used"
rows until the file is re-saved.

This gives you the real last row with data:
activesheet.usedrange.SpecialCells(xlCellTypeLastC ell).Row

HTH

Bill Benson
http://www.xlcreations.com

"JN" wrote in message
...
Hi,

I am trying to set up a macro to fill up formulas two rows below the
last
row of a worksheet. The problem is this number of rows is different
each
week. I have a hard time making this macro works for different week.
Thanks.

Below is the code:

Sub Macro13()
'
ActiveCell.FormulaR1C1 = "Total"
Range("M204").Select
ActiveWindow.SmallScoll Down:=12
Range("M199").Select
Selection.Copy
Range("M204").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUM(RC[1])-SUM(RC[2])"
Range("M205").Select
ActiveWindow.SmallScroll ToRight:=1
Range("N204").Select
ActiveCell.FormulaR1C1 = "=+R[-2]C/R[-203]C"
Range("N204").Select
Selection.AutoFill Destination:=Range("N204:S204"),
Type:=xlFillDefault
Range("N204:S204").Select
Selection.NumberFormat = "#,##0.0_);[Red](#,##0.0)"
Selection.Font.Bold = True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Range("M204").Select
End Sub

Thank you very much for everyone's help!!!!

"Tom Ogilvy" wrote:

From your code, I would suggest something like this. This assumes all
columns have data down to the same row so the formula will all be on the
same row (appears to be consistent with your recorded code).

Sub BuildFormulas()
Set rng = Cells(Rows.Count, "M").End(xlUp)
Set rng = rng.Offset(2, 0)
With rng.Resize(1, 7) ' M to S
.FormulaR1C1 = "=R[-2]C/R1C"
.NumberFormat = "#,##0.0_);[Red](#,##0.0)"
.Font.Bold = True
End With
ActiveSheet.Outline.ShowLevels RowLevels:=2
End Sub

In my test, if the data ended in Row 202, then the formula in M204 was

=M202/M$1

Similar formulas were entered in N202 to S202

If this isn't the correct formula, you would need to adjust the formula
portion.

--
Regards,
Tom Ogilvy




"JN" wrote in message
...
Does it mean by replacing Range("M204") with
"activecell.SpecialCells(xlCellTypeLastCell).R ow" will work? Could you
tell
me where I should put this code in? I am a beginner in this. Can I apply
the
same code for other specific cell, like N204, in the macro? Thanx.



"William Benson" wrote:

The last row that was ever "touched" can be found using
activecell.SpecialCells(xlCellTypeLastCell).Row

however, sometimes cleared contents from cells are still treated as
"used"
rows until the file is re-saved.

This gives you the real last row with data:
activesheet.usedrange.SpecialCells(xlCellTypeLastC ell).Row

HTH

Bill Benson
http://www.xlcreations.com

"JN" wrote in message
...
Hi,

I am trying to set up a macro to fill up formulas two rows below the
last
row of a worksheet. The problem is this number of rows is different
each
week. I have a hard time making this macro works for different week.
Thanks.

Below is the code:

Sub Macro13()
'
ActiveCell.FormulaR1C1 = "Total"
Range("M204").Select
ActiveWindow.SmallScoll Down:=12
Range("M199").Select
Selection.Copy
Range("M204").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUM(RC[1])-SUM(RC[2])"
Range("M205").Select
ActiveWindow.SmallScroll ToRight:=1
Range("N204").Select
ActiveCell.FormulaR1C1 = "=+R[-2]C/R[-203]C"
Range("N204").Select
Selection.AutoFill Destination:=Range("N204:S204"),
Type:=xlFillDefault
Range("N204:S204").Select
Selection.NumberFormat = "#,##0.0_);[Red](#,##0.0)"
Selection.Font.Bold = True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Range("M204").Select
End Sub

Perhaps my explanation was not originally clear because the problem is so
well known and I didn't think I would need to defend the facts or provide
all the background.

Excel 2003 does no better in the situation which is so often posted here [
situation 3 below]. Norman Jones has shown you one instance where your
solution doesn't work (and you agreed to that). Here is another to
illustrate. On a completely blank/new worksheet run this code:

Sub ABCD()
' tested in xl2003
Cells.Clear
rw1 = Range("A1").SpecialCells(xlCellTypeLastCell).Row
rw2 = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastC ell).Row
Rows(1000).RowHeight = 20
Cells.Clear
rw3 = Range("A1").SpecialCells(xlCellTypeLastCell).Row
rw4 = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastC ell).Row
MsgBox rw1 & ", " & rw2 & ", " & rw3 & ", " & rw4
End Sub

Three conditions can prevail:

1) xlCelltypeLastCell correctly reports the last cell actually containing
data (so any range anchor is sufficient)

2) xlCelltypeLastCell overstates the last cell actually containing data
(that cell/row and probably more are empty), but this can be reset by
issuing the command
Activesheet.UsedRange
[Your suggestion is particularly applicable here, but because the
Activesheet.UsedRange part resets the used range as part of its work]

3) xlCelltypelastCell overstates the last cell actually containing data
(same as 2), but this is unchanged by issuing
Activesheet.UsedRange
or by saving or by saving and closing/reopening the workbook. (without
performing other actions to get to condition 2)

In either 1 or 2, your suggestion of using
activesheet.UsedRange(xlcelltypelastcell) is a good suggestion

In case 3, it is not. (and this is the situation most often posted in this
newsgroup)

Because of 3, such techniques as (posted by Norman Jones in this thread, but
been around since at least 1997)

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

have been developed and manual and programmatic methods such as

http://www.contextures.com/xlfaqApp.html#Unused
Debra Dalgleish's site

have been documented. In **general** the use of UsedRange or
specialcells(xlCellTypeLast) or both as you suggested are not good solutions
although when one knows they will be accurate, they are easy and useful.

As I see it, this is the "High Truth"

hope I have clearly stated it now.

--
Regards,
Tom Ogilvy