[SOLVED] Help with Macro

[JN]

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

[William Benson]

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(xlCellTypeLastCell).Row

HTH

Bill Benson
http://www.xlcreations.com

"JN" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

[Norman Jones]

Hi Bill,

> This gives you the real last row with data:
> activesheet.usedrange.SpecialCells(xlCellTypeLastCell).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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
news:%[email protected]...
> 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(xlCellTypeLastCell).Row
>
> HTH
>
> Bill Benson
> http://www.xlcreations.com

[Tom Ogilvy]

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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
news:%[email protected]...
> 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(xlCellTypeLastCell).Row
>
> HTH
>
> Bill Benson
> http://www.xlcreations.com
>
> "JN" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
>
>

[JN]

Does it mean by replacing Range("M204") with
"activecell.SpecialCells(xlCellTypeLastCell).Row" 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(xlCellTypeLastCell).Row
>
> HTH
>
> Bill Benson
> http://www.xlcreations.com
>
> "JN" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
>
>
>

[William Benson]

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(xlCellTypeLastCell).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(xlCellTypeLastCell).Row

Result is 2 (NOT 25!!!)

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

Bill





"Norman Jones" <[email protected]> wrote in message
news:%[email protected]...
> Hi Bill,
>
>> This gives you the real last row with data:
>> activesheet.usedrange.SpecialCells(xlCellTypeLastCell).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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
> news:%[email protected]...
>> 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(xlCellTypeLastCell).Row
>>
>> HTH
>>
>> Bill Benson
>> http://www.xlcreations.com
>
>

[Norman Jones]

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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
news:[email protected]...
> 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(xlCellTypeLastCell).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(xlCellTypeLastCell).Row
>
> Result is 2 (NOT 25!!!)
>
> I cannot explain your results but I can tell you that I have tested mine!
>
> Bill
>
>

[William Benson]

Norman,

I see, thanks for the correction!

Can you help the OP then since I cannot?

B.

"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> 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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
> news:[email protected]...
>> 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(xlCellTypeLastCell).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(xlCellTypeLastCell).Row
>>
>> Result is 2 (NOT 25!!!)
>>
>> I cannot explain your results but I can tell you that I have tested mine!
>>
>> Bill
>>
>>
>
>

[William Benson]

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(xlCellTypeLastCell).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" <[email protected]> wrote in message
news:[email protected]...
> 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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
> news:%[email protected]...
>> 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(xlCellTypeLastCell).Row
>>
>> HTH
>>
>> Bill Benson
>> http://www.xlcreations.com
>>
>> "JN" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> >
>> >
>>
>>
>
>

[Norman Jones]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

[Tom Ogilvy]

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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
news:%[email protected]...
> 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(xlCellTypeLastCell).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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
> > news:%[email protected]...
> >> 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(xlCellTypeLastCell).Row
> >>
> >> HTH
> >>
> >> Bill Benson
> >> http://www.xlcreations.com
> >>
> >> "JN" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
> >> >
> >> >
> >>
> >>
> >
> >
>
>

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> Does it mean by replacing Range("M204") with
> "activecell.SpecialCells(xlCellTypeLastCell).Row" 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(xlCellTypeLastCell).Row
> >
> > HTH
> >
> > Bill Benson
> > http://www.xlcreations.com
> >
> > "JN" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> > >
> >
> >
> >

[William Benson]

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
> news:%[email protected]...
>> 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(xlCellTypeLastCell).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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
>> > news:%[email protected]...
>> >> 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(xlCellTypeLastCell).Row
>> >>
>> >> HTH
>> >>
>> >> Bill Benson
>> >> http://www.xlcreations.com
>> >>
>> >> "JN" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

[JN]

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" <[email protected]> wrote in message
> news:[email protected]...
> > Does it mean by replacing Range("M204") with
> > "activecell.SpecialCells(xlCellTypeLastCell).Row" 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(xlCellTypeLastCell).Row
> > >
> > > HTH
> > >
> > > Bill Benson
> > > http://www.xlcreations.com
> > >
> > > "JN" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > > >
> > > >
> > >
> > >
> > >
>
>
>

[Tom Ogilvy]

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(xlCellTypeLastCell).Row
Rows(1000).RowHeight = 20
Cells.Clear
rw3 = Range("A1").SpecialCells(xlCellTypeLastCell).Row
rw4 = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).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