VBA Code question: Define last row in sheet

[Excel789]

<!-- / icon and title --><!-- message -->I'm attempting to write code that will base the print area for a spreadsheet on certain criteria. I think I'll be able to manage this if I can name the last row in the spreadsheet "Final Row" and make it equal to the result of a formula contained in cell M2 of the spreadsheet.

Any idea of how/whether this can be accomplished?

Thanks very much!

 

[neddiexyz]

Hi,

To find the last row in your sheet try:

For Each Cell In [A65536:IV65536]
If Cell.End(xlUp).Row > Count Then
Count = Cell.End(xlUp).Row
End If
Next Cell

MsgBox "" & Count

If you follw this with:

ActiveCell.Offset(1, 0).Select

Selection.Insert Shift:=xlDown
ActiveCell.FormulaR1C1 = "Final Row"

This will mark the next blank row (R1C1) with Final Row.

Hope that thios helps.

Ned.

 

[neddiexyz]

Hi,

To find the last row in your sheet try:

For Each Cell In [A65536:IV65536]
If Cell.End(xlUp).Row > Count Then
Count = Cell.End(xlUp).Row
End If
Next Cell

MsgBox "" & Count

If you follw this with:

ActiveCell.Offset(1, 0).Select

Selection.Insert Shift:=xlDown
ActiveCell.FormulaR1C1 = "Final Row"

This will mark the next blank row (R1C1) with Final Row.

Hope that thios helps.

Ned.

Hope this helps

 

[Excel789]

Thanks, but this won't work because I have cells in the spreadsheet that contain formulas that I don't want to capture because they evaluate to "". A range of varying size at the top of the spreadsheet is returned, and below that are the blank cells with formulas in them. Cell M2 contains a formula that will always return a number equal to the number of visible rows in the sheet. If I could express that in the VBA Code, I'd be in good shape, I think.