Macro to insert a new row at the bottom of a selection

AlisaA

Board Regular
Joined
Mar 17, 2010
Messages
193
<TABLE style="WIDTH: 380pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=507><COLGROUP><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" span=2 width=61><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1462" span=4 width=40><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" span=2 width=75><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ccc0da; WIDTH: 46pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl78 height=17 width=61>Test</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccc0da; WIDTH: 46pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl68 width=61>Mat'l</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccc0da; WIDTH: 56pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl79 width=75></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccc0da; WIDTH: 30pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl80 width=40></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccc0da; WIDTH: 30pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl80 width=40></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccc0da; WIDTH: 30pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl80 width=40></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccc0da; WIDTH: 30pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl80 width=40></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccc0da; WIDTH: 56pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl68 width=75>Lift No. or</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccc0da; WIDTH: 56pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 width=75>Moisture</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ccc0da; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 height=18>Test No.</TD><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccc0da; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>Mat'l No.</TD><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccc0da; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" class=xl76 colSpan=5 align=middle>Location</TD><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccc0da; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71>Elevation</TD><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccc0da; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>Content (%)</TD></TR><TR style="HEIGHT: 26.1pt; mso-height-source: userset" height=34><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 26.1pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73 height=34>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 176pt; BORDER-TOP: windowtext 2pt double; BORDER-RIGHT: black 0.5pt solid" class=xl85 width=235 colSpan=5></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl75></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81></TD></TR><TR style="HEIGHT: 26.1pt; mso-height-source: userset" height=34><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 26.1pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73 height=34>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 176pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl82 width=235 colSpan=5></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl75></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81></TD></TR><TR style="HEIGHT: 26.1pt; mso-height-source: userset" height=34><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 26.1pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73 height=34>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 176pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl82 width=235 colSpan=5></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl75></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81></TD></TR></TBODY></TABLE>

I would like to have a macro that would insert a row at the bottom of the "table" (is not a true Excel table) above.

With just a quick Record Macro, this is what I got:

Code:
Sub Macro2()
'
' Macro2 Macro
'
'
    ActiveSheet.Unprotect
    Range("A31").Select
    Selection.End(xlDown).Select
    Rows("43:43").Select
    Selection.Copy
    Rows("44:44").Select
    Selection.Insert Shift:=xlDown
    Range("A44").Select
    Application.CutCopyMode = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowInsertingRows:=True
End Sub

Starting with cell A31 and using the Selection.End(xlDown) is fine, because that will pick up the proper last row in the range. However, I need the row numbers to be dynamic. Eg. - select the last row in the range, copy it, and then insert the copied row below it.

I'm sure I could figure this out, as I'm sure there is much helpful stuff in the boards, but I am hoping you all could save me some hunting around.

I appreciate the help, as always!
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
Im not sure what you are trying to achieve but the following will copy a table 25 times with 2 blank rows inbetween each table. Can you adapt this?

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> CopyTab1()<br>    <SPAN style="color:#00007F">Dim</SPAN> rws <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#007F00">'Copy a table 25 times with 2 rows inbetween</SPAN><br>    <br>    <SPAN style="color:#00007F">With</SPAN> Range("A2:I17")<br>        rws = .Rows.Count + 2<br>        .Resize(rws).Copy Destination:=.Offset(rws).Resize(rws * 25)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 

AlisaA

Board Regular
Joined
Mar 17, 2010
Messages
193
Ok this is what I have thus far:

Code:
Sub Macro2()
'
' Macro2 Macro
'
'
    Dim copyrw As Long
    Dim pasterw As Long
 
    ActiveSheet.Unprotect
    With Range("A31", Selection.End(xlDown))
      copyrw = .Rows.Count
      pasterw = .Rows.Count + 1
    Rows(copyrw).Select
    Selection.Copy
    Rows(pasterw).Select
    Selection.Insert Shift:=xlDown
    Range("A" & pasterw).Select
    Application.CutCopyMode = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowInsertingRows:=True
 
    End With
 
End Sub

It's not quite doing what I want though - it is inserting rows in random places on the spreadsheet.

The table that I had in my first post has the first Test No. starting in cell A31. The file starts out with 13 rows in that table. So, if I were to run this macro, I would want it to go to the last row in the table (row 43), copy that row, and insert/copy it into row 44. I'd like the user to be able to do this whenever they need to add a row to this table, so the last row of the table may change often.

Right now, this macro is copying & inserting row 13...?

Thanks for your help!!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,482
Messages
5,596,402
Members
414,063
Latest member
N_Bates

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top