Using VBA to add/delete blank rows below a selected cell/row to a certain row number?

[ubergreen]

I want to be able to add a button that can add blank rows to a selected cell on my sheet. For example

Order Number	Color	Size
1	Blue	L
	Green	S
2	Pink	M
3	Grey	XL

Then I want to be able to select say cell either A3/B3/etc and press a button that adds two blank rows below to result in the below

Order Number	Color	Size
1	Blue	L
	Green	S
2	Pink	M
3	Grey	XL

Then after that is complete, i want to be able to push another button, while selecting cell A5/B5/etc and then press a button that deletes all of the rows above it to move it to the top underneath the header as below:

Order Number	Color	Size
2	Pink	M
3	Grey	XL

 

[Joe4]

Welcome to the Board!

Here is code for your row insert:

Sub InsertTwoBlankRowsBelow()
    Dim r As Long
    r = ActiveCell.Row
    Range(Cells(r + 1, "A"), Cells(r + 2, "A")).EntireRow.Insert
End Sub

And here is code for the row delete:

Sub DeleteRowsAbove()
    Dim r As Long
    r = ActiveCell.Row
    If r > 2 Then Range(Cells(2, "A"), Cells(r - 1, "A")).EntireRow.Delete
End Sub

Just attach that code to your buttons, and you should be good to go!

 

[ubergreen]

Welcome to the Board!

Here is code for your row insert:

Sub InsertTwoBlankRowsBelow()
    Dim r As Long
    r = ActiveCell.Row
    Range(Cells(r + 1, "A"), Cells(r + 2, "A")).EntireRow.Insert
End Sub

And here is code for the row delete:

Sub DeleteRowsAbove()
    Dim r As Long
    r = ActiveCell.Row
    If r > 2 Then Range(Cells(2, "A"), Cells(r - 1, "A")).EntireRow.Delete
End Sub

Just attach that code to your buttons, and you should be good to go!

Thanks for the codes. The codes worked well.

I actually just realized that I wrote my question incorrectly. What I need it to do is add blank rows up to a specific row number. Using the example above, I would like to be able to select maybe the last item in the row and then be able to click a button so that it will add blank rows up to row # 5 every single time. Regardless of its initial starting row position.

However, thinking about possibly being able to automate it more, I don't know if this is something that is possible with either VBA or PQ. Would it somehow be possible to set it up in PQ so that each of the "Order Number" groups will have blank rows added to the end of each group so that each group always takes up exactly 5 rows. Example below

Then with PQ, have it automatically output to look like the following below:

Then from here, if I can use VBA to create a button that will always delete only the top 5 rows, so that it will essentially move the next order group to the top 5 rows and so on. The top row with order number, color and size will be table headers, so I don't think they will be affected by the VBA code from what I understand.

 

[Joe4]

Your follow-up question is VERY different than your original question (and as you said, the initial supply answered the actual question you posted).
There, you should post it in a new thread/question of its own (especially since you are now introducing Power Query, which I do not work with).

Also note that there is a forum here where you can ask Power Query questions: Power Tools

 

[ubergreen]

Your follow-up question is VERY different than your original question (and as you said, the initial supply answered the actual question you posted).
There, you should post it in a new thread/question of its own (especially since you are now introducing Power Query, which I do not work with).

Also note that there is a forum here where you can ask Power Query questions: Power Tools

You're right. Thank you for the help!