Trouble using macros to insert rows

  • Thread starter Thread starter thegetch1
  • Start date Start date
T

thegetch1

Hi,

I have 3 buttons that insert new rows in 3 different sections of the
spreadsheet. Whenever I insert a row, the subsequent rows referenced in
the other macros are now off. For example, if I add 2 new rows to
sections 1 with button 1, buttons 2 and 3 will now add rows 2 rows
above where I want them. I tried coding a variable to track how many
times each button is pressed, but I don't know how to add that number
to a row. They are different types. I'm a novice with VB so that could
be problem. Any help you can give would be much appreciated.

THANKS!!
 
Please post the code for each button, and tell us what range is section
1, 2, and 3. Also, you say "I tried coding a variable to track how
many times each button is pressed, but I don't know how to add that
number to a row." Did you try this to troubleshoot the Button 1-2-3
problem, or is this a different requirement?
 
Dave said:
Please post the code for each button, and tell us what range is section
1, 2, and 3. Also, you say "I tried coding a variable to track how
many times each button is pressed, but I don't know how to add that
number to a row." Did you try this to troubleshoot the Button 1-2-3
problem, or is this a different requirement?
Click to expand...

Thanks for the response.

The code is very simple, just recorded it:

Sub addMilestone1()

Rows("20:20").Select
Selection.Insert Shift:=xlDown
Range("A20").Select
End Sub

Sub addMilestone2()

Rows("21:21").Select
Selection.Insert Shift:=xlDown
Range("A21").Select
End Sub

Sub addMilestone3()

Rows("22:22").Select
Selection.Insert Shift:=xlDown
Range("A22").Select
End Sub

As you can see the rows to select are hardcoded. Hence, when I use
addMilestone1, the other 2 milestone macros won't change the row they
should be inserted at.

For example: if I run addMilestone1 twice, it will add 2 rows at row
20. It will shift all other rows below it down by 2. So, when I go to
run the other macros, they insert 2 rows above where they should.

I tried to track the number of times a button was pressed so that I
could add that number to the row in each macro to offset for any rows
that have been inserted. Problem here is that I don't know how to add
anumber to Rows("20:20").Select. If I put the variable in place of the
20's, it says that it is a type mismatch which makes sense because it
is looking for a row and I am handing it a number.

Hope that answered your questions, I really appreciate any help you can
offer.

Getch
 
Here's one way to do it: create a named range for each of the three
sections: I've called them Section1, Section2, and Section3 for this
example. Each named range should be 1 row above the row each section
starts on. Then change your code to the code that follows. The named
ranges for sections 2 and 3 will move, and thereby they are dynamic,
when rows are inserted above them, which will relieve the need to keep
track of how many rows have been inserted. Note: if you use a
different named range naming format, you'll need to change the code to
match.

Let me know what you think- will this do it for you?


Sub addMilestone1()
Application.Goto Reference:="Section1"
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
Range("a" & Selection.Row).Select
End Sub

Sub addMilestone2()
Application.Goto Reference:="Section2"
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
Range("a" & Selection.Row).Select
End Sub

Sub addMilestone3()
Application.Goto Reference:="Section3"
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
Range("a" & Selection.Row).Select
End Sub
 
THANK YOU!!!!!!!!!!!!!!!

I've worked with named ranges before. Genious to use them in this
manner, never would have thought of that. Works PERFECTLY! I don't know
how to thank you, but I appreciate this so much.

Take care!
Getch
 
Back
Top