ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Macro: Insert Blank Row with formulas uses absolute position probl (https://www.excelbanter.com/excel-discussion-misc-queries/234323-macro-insert-blank-row-formulas-uses-absolute-position-probl.html)

steveb

Macro: Insert Blank Row with formulas uses absolute position probl
 
My problem:
Creating a macro to insert a new blank row, then fill that row with the
formulas/formatting from the row above. (ie I'm trying to keep my file size
at the smallest it needs to be for the data in it)

Simple, yes. But having recorded the Macro Excel (vb) always inserts the row
at the same row number (ie always inserts the new row at row 18), whereas I
need the macro to create a row beneath the last row with data in it which
could be row 60 or 70 etc.

I think I need the macro to refer to a relative row position rather than an
absolute row position. Can I do that?

Mike H

Macro: Insert Blank Row with formulas uses absolute position probl
 
hi,

How about this

LastRow = ActiveSheet.UsedRange.Rows.Count
Rows(LastRow).Copy
Rows(LastRow).Offset(1).PasteSpecial Paste:=xlPasteAll

Mike

"steveb" wrote:

My problem:
Creating a macro to insert a new blank row, then fill that row with the
formulas/formatting from the row above. (ie I'm trying to keep my file size
at the smallest it needs to be for the data in it)

Simple, yes. But having recorded the Macro Excel (vb) always inserts the row
at the same row number (ie always inserts the new row at row 18), whereas I
need the macro to create a row beneath the last row with data in it which
could be row 60 or 70 etc.

I think I need the macro to refer to a relative row position rather than an
absolute row position. Can I do that?



All times are GMT +1. The time now is 03:46 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com