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

A

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!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
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>
Click to expand...
 
Upvote 0
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!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
829
gvsumich
G
E
  • Question
VBA for Web Scraping help
Replies
0
Views
593
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
E
  • Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
349
esben
E

Forum statistics

Threads
1,214,924
Messages
6,122,294
Members
449,077
Latest member
Rkmenon

Share this page

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
Back
Top