The Classic 'Add New Row Button' Macro

dannyok90

Board Regular
Joined
Aug 30, 2016
Messages
105
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

Well-known Member
Joined
Nov 16, 2013
Messages
521
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:

dannyok90

Board Regular
Joined
Aug 30, 2016
Messages
105
Absolutely brill! I made a few adjustments to suit my needs but you’ve taught me a lot there!

Cheers coops
 

Trig88

New Member
Joined
Jan 13, 2019
Messages
1
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

Well-known Member
Joined
Nov 16, 2013
Messages
521
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
 

Forum statistics

Threads
1,082,020
Messages
5,362,713
Members
400,686
Latest member
Aakash

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top