I made a macro record and made sure to use relative references, I made rows to identify how may to insert via select then insert rows. But it just became the dullest macro coz it refers to my references and makes it absolute the next time I would run it, it would use the last references as if it were absolute.
eg. I made a column that I would select a number of rows down, separated by a space, so these are block rows which I intended to be the basis of the number of rows I would insert. But the macro would deem them as absolute reference, so I tried editing the vba code. I replaced the ranges to activecell.offset( the movements I made in selecting the block rows)-worked fine up until the selection of rows to insert. I want it to be relative to the number of rows in each blocks I made. Thank you so much.
The situation is not clear enough to me and there is no sample file.
However, In general
1- A relative macro runs RELATIVE to a Starting cell
Beside highlighting "Use Relative Reference" on the Developer Tab, you have to note which cell was selected when you started the recording.
When you run the Macro, it will repeat the recorded steps relative to the cell you select when running it.
2- Because the macro recorder doesn't necessarily create an efficient code, then try to shorten the code by using shortcuts while recording, like instead of using a drop down menu command to insert Rows, you can use the shortcut CTRL + (Plus sign on the NUMERIC keypad) Hope that helps
Best Response confirmed by
ihatebills (Occasional Contributor)
Sub Macro1() ' ' Macro1 Macro ' ' Keyboard Shortcut: Ctrl+q ' ActiveCell.Offset(0, -1).Range("A1").Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlDown).Select ActiveCell.Offset(0, 1).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Rows("1:14").EntireRow.Select ActiveCell.Activate Selection.Insert Shift:=xlDown End Sub
as per code above you can see activecell.rows("1:14") is absolute I want it to be
based on the number of selected row my macro did.
I put spaces in between rows to identify how many rows to select. But in this macro it makes it absolute the next time I would run it even though it is just 3 rows identified it will still insert 14 rows!??