The Classic 'Add New Row Button' Macro

[dannyok90]

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

 

[cooper645]

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.

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.

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

 

[dannyok90]

Absolutely brill! I made a few adjustments to suit my needs but you’ve taught me a lot there!

Cheers coops

 

[Trig88]

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!!

 

[cooper645]

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