Macro to insert new row at bottom of table, find highest value in column a & add 1
Category: [Excel] Demo Available
Macro to insert new row at bottom of table, find highest value in column A and add 1
Requirement:
The user has a large table that has data in it. Currently column A is used as an "Unique ID". This number was initially an auto-fill that is now just values. The user wants to be able to run a macro to do the following:
Insert a new row at the bottom of the table
Insert a value into Column A that is 1 greater than the largest value in Column A - regardless of sort location
The user currently has a macro that works to do the following:
Insert a new row at the bottom of the table
Insert a value into Column A that is 1 greater than the previous value/row.
Code:
Sub Insert_New_Rows() Dim Lr As Integer
Lr = Range("A" & Rows.Count).End(xlUp).Row
Rows(Lr + 1).Insert Shift:=xlDown
Cells(Lr + 1, "A") = Cells(Lr, "A") + 1
Rows(Lr).Copy
Rows(Lr + 1).PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End Sub
Solution:
Code:
Sub Insert_New_Rows()
Dim Lr As Integer
Lr = Range("A" & Rows.Count).End(xlUp).Row
Rows(Lr + 1).Insert Shift:=xlDown
Cells(Lr + 1, "A") = Application.Max(Columns(1).SpecialCells(2)) + 1
Rows(Lr).Copy
Rows(Lr + 1).PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by KjBox.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to create VBA code to go down a column and check if values are present in a table |
| How to count cells in a dynamic range matching two criteria given in table headers |
| How to create an Excel Pivot Table calculated field |
| How to use advanced lookup: Multiple criteria when looking up values in a table |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.