The Classic 'Add New Row Button' Macro

dannyok90

Board Regular
Joined
Aug 30, 2016
Messages
115
Hi Guys,

I'm aware there is tons of information on the net about this and I'm sort of biting the bullet asking such a generic question.

I have a spreadsheet which is a template at the beginning of its life so there is literally no data in there what so ever.
I'm wondering if someone could help me with the code for a new row button that sits at the bottom of the table?

at the moment I have two lines (1&2) ready for data to be populated, the second of the two is a copy down row so users can drag a new row down without copying the information from the last entry.

the way the entries are numbered is by using the =ROW()-1 function so I'm thinking if I could get the button to sit underneath line one and use line two as the copying line I could hide that row and just have the code insert the new row underneath line one and the sequential numbers would correct themselves.

the button would move as the table expanded ideally..

I'm starting to understand code, but its very early days for me so your input is massively appreciated - there maybe a much better way of achieving what I'm trying to achieve but this is the best idea I could come up with in terms of the mechanics of the VBA.

Another point to note is that this spreadsheet is protected to avoid users changing formula cells, there is two cells along the row that are locked and that's the need for this button, because they cant highlight the entire row and copy it down when protection is on.

Thanks Guys :D
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Hi there, I added a button with a row between it and the bottom of the table, ensure the properties are set to move with the cells. (this ensures it moves as your table expands)

I then added the following Code to the button.

Code:
Sub AddRow()
Dim sht As Worksheet
Dim LastRow As Long
Set sht = ActiveSheet
Application.ScreenUpdating = False
sht.Unprotect
LastRow = sht.Range("A1").CurrentRegion.Rows.Count
Range("A" & LastRow).EntireRow.Insert
sht.Protect
Application.ScreenUpdating = True
End Sub

This should achieve your aim.


Below is the same code broken down to explain what each part does to help you understand.

Code:
Sub AddRow()

[COLOR=#008000]'declare sht to mean Worksheet[/COLOR]
Dim sht As Worksheet

[COLOR=#008000]'declare lastrow to be an integer up to a large size[/COLOR]
Dim LastRow As Long

[COLOR=#008000]'assign the Active Sheet to sht in the memory from here[/COLOR]
Set sht = ActiveSheet

[COLOR=#008000]'stop the screen showing the following steps[/COLOR]
Application.ScreenUpdating = False

[COLOR=#008000]'unprotect the active sheet [/COLOR]
sht.Unprotect

[COLOR=#008000]'find the last row by counting the rows with data from cell A1[/COLOR]
LastRow = sht.Range("A1").CurrentRegion.Rows.Count

[COLOR=#008000]'insert a new row at the point of the last row[/COLOR]
Range("A" & LastRow).EntireRow.Insert

[COLOR=#008000]'re-protect the active sheet[/COLOR]
sht.Protect

[COLOR=#008000]'update the screen with what just happened[/COLOR]
Application.ScreenUpdating = True


End Sub
Coops
 
Last edited:
Upvote 0
Hi All,

I'm starting to get to grips with using Macros.

The above macro is great and is the closest i have come achieving what i would like to accomplish, however it doesn't completely work on my spreadsheet. I too have a number of protected cells across the rows on my table. My table starts at A6, and finishes at Y6, but the macro isn't inserting a row at the bottom of the table for some reason.

Any help really would be appreciated!!
 
Upvote 0
Try

Sub AddRow()
Dim sht As Worksheet
Dim LastRow As Long
Set sht = ActiveSheet
Application.ScreenUpdating = False
sht.Unprotect
LastRow = sht.Range("A6").CurrentRegion.Rows.Count
Range("A" & LastRow).EntireRow.Insert
sht.Protect
Application.ScreenUpdating = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

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