Solved How to use a macro to add a row at different points

July 26, 2013 at 06:24:44
Specs: Windows XP
Hello!
I'm trying to use a macro to add a row into Excel. I have recorded the macro and it works ok but when I have tried to record another macro to insert rows lower down in the sheet, I'm finding that the point the rows are being added is sometimes wrong, depending on how many rows I have added with Macro 1. The macro I am using is:

Sub Adddaterows()
'
' Adddaterows Macro
' Add date rows
Rows("50:50").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.FillDown
End Sub


What I want is for Rows("50:50").Select to be updated depending on how many rows I have added using the previous macro. So if no rows added with the first macro, I want to add at row 50 using the second macro. but if say 3 rows added then I want to add at row 53.


See More: How to use a macro to add a row at different points

Report •

#1
July 26, 2013 at 07:42:26
✔ Best Answer
First, a posting tip: Before posting anymore VBA code in this forum, please click on the blue line at the bottom of this post and read the instructions found via that link.

As for your question, there is no way for the code itself to know how many rows you added previously. VBA can't "remember" anything from the previous time the code was executed.

That said, I have a couple of questions:

You said: "So if no rows added with the first macro, I want to add at row 50 using the second macro."

How can no rows be added? The code Selects Row 50 and inserts a row, If the macro is run, it will insert a row. What second macro are you referring to?

You also said: "but if say 3 rows added then I want to add at row 53."

How can the macro add 3 rows? All it can do is add a single row below Row 50 each time it is run. Granted, if you ran it 3 times, it will insert 3 rows, but each of those rows will be inserted below Row 50.

Perhaps if you explained exactly what you are trying to do, we could offer some specific aadvice.

If you simply want to insert a row at the Selection point, you can comment out or delete the Rows("50:50") line and a single row will be insert at whatever row you have selected.

Sub Adddaterows()
'
' Adddaterows Macro
' Add date rows
' Rows("50:50").Select

  Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
  Selection.FillDown
End Sub

If you don't want to select a row prior to running the macro, then we need to keep track of where the next new row should be inserted. As I said, VBA can't store that information, but VBA can tell Excel to keep track where the next row should go. That could be done by having the code store the row number in a cell, and then use that value to determine where to insert the next new row.

For example, before you run the code for the first time, put 50 in B1 then run the following code. Each time you run it, it will add 1 to B1 and then use the new value as the next insertion point:

Sub Adddaterows1()
'
' Adddaterows Macro
' Add date rows

'Insert Row and Filldown
  Rows(Range("B1")).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
  Rows(Range("B1")).FillDown
'Increment Row Counter
  Range("B1") = Range("B1") + 1
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 29, 2013 at 01:31:30
DerbyDad, wow thank you! That works perfectly. Thanks for deciphering my question.

message edited by godfreyellis


Report •

#3
July 29, 2013 at 03:54:48
I offered 2 suggestions. Just curious...which one did you use?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
July 29, 2013 at 05:02:08
I used the second one. I entered a calculation into excel cell P1 to sum the number of rows added using the first macro and then add 40. This total is then picked up by the macro you provided which adds a row at reference 46 + 1 (assuming there were 6 rows added using the first macro).

 Sub Adddaterows()
'
' Adddaterows Macro
' Add date rows
'
UnprotectAll
'Insert Row and Filldown
  Rows(Range("P1")).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
  Rows(Range("P1")).FillDown
'Increment Row Counter
  Range("P1") = Range("P1") + 1
 ProtectAll
 
End Sub

Thanks again


Report •

Ask Question