Adding a row

Dan in Lowell

New Member
Joined
Sep 29, 2006
Messages
14
having had such great luck with my last question..........

I have a spreadsheet, Column A is titled "ID" and the rows are sequentially numbered. (some rows are hidden). Columns B through G contain data that pertain to that ID. I would like a macro that will add a new ID to the bottom of the list and automatically populate the ID number fieild taking into account that the greatest of the ID's may be on a hidden row. I would then like the cursor to be positioned in column B of that new row. If possible, I would like the formatting of the row above to be copied to the new row as well.

Anyone that good?
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Sub myNewRow()
'Standard Module code, like: Module1.
Dim myRow&, myNextID&
Dim myIDRng As Range

'Select last row!
myRow = ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0).Row
ActiveSheet.Rows(myRow).Select

'Get highest ID Number!
Set myIDRng = ActiveSheet.Range(Cells(2, 1), Cells(ActiveSheet.Range("A65536").End(xlUp).Row, 1))
myNextID = Application.WorksheetFunction.Max(myIDRng)

'Insert new row at bottom.
ActiveCell.EntireRow.Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown

'Copy all except the Value & Validation of the next ROW.
ActiveCell.Offset(rowOffset:=-1, columnOffset:=0).Activate
'Note: Change "rowOffset:=" to "-1" to copy next ROW up & "1" for next ROW down!
ActiveCell.EntireRow.Select
Selection.Copy

'Paste all except the Value & Validation to the new ROW.
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
'Note: If "rowOffset:=" above was changed, do the opposite here!

ActiveCell.EntireRow.Select

'You can add or remove as many of the pasteSpecial paste properties as you want.
'Or use: Paste:=xlAll, to paste all options.
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteComments, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'Add next ID:
ActiveSheet.Range("A65536").End(xlUp).Select
Selection.Value = myNextID + 1
Selection.Offset(0, 1).Select
Application.CutCopyMode = False
End Sub
 

Dan in Lowell

New Member
Joined
Sep 29, 2006
Messages
14
Joe,

That's fantastic!

Currently, it is copying all of the text from the row above. Is there a way to have only the "A" cell of that new row populated with the new ID and the "D" cell populated with the current date? All other cells should be blank except for the formatting.

Thanks again for you help This is great
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Sub myNewRow()
'Standard Module code, like: Module1.
Dim myRow&, myNextID&
Dim myIDRng As Range

'Get highest ID Number!
Set myIDRng = ActiveSheet.Range(Cells(2, 1), Cells(ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0).Row, 1))
myNextID = Application.WorksheetFunction.Subtotal(4, myIDRng)

'Select last row and copy it!
myRow = ActiveSheet.Range("A65536").End(xlUp).Row
ActiveSheet.Rows(myRow).Select
ActiveCell.EntireRow.Select
Selection.Copy

'You can add or remove as many of the pasteSpecial paste properties as you want.
'Or use: Paste:=xlAll, to paste all options.

ActiveCell.Offset(1, 0).EntireRow.Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'Add next ID to new row:
ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0).Select
Selection.Value = myNextID + 1

'Select next cell.
ActiveSheet.Range("A65536").End(xlUp).Offset(0, 1).Select
Application.CutCopyMode = False
End Sub
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Dan,
Per your PM. This code will un-hide all the rows, get the next ID number, add the new row and next ID then re-hide all the rows that have any form of "Closed" in column "B" and end with the next input cell selected.


Sub myNewRow()
'Standard Module code, like: Module1.
Dim myRow&, myNextID&
Dim myIDRng As Range, myRng As Range

'Get highest ID Number Range!
Set myIDRng = ActiveSheet.Range(Cells(2, 1), Cells(ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0).Row, 1))

'Un-hide hidden rows for calculations!
For Each Cell In myIDRng
If Cell.EntireRow.Hidden = True Then ActiveSheet.Rows(Cell).Hidden = False
Next Cell

'Get highest ID number!
myNextID = Application.WorksheetFunction.Max(myIDRng)

'Select last row and copy it!
myRow = ActiveSheet.Range("A65536").End(xlUp).Row
ActiveSheet.Rows(myRow).Select
ActiveCell.EntireRow.Select
Selection.Copy

'You can add or remove as many of the pasteSpecial paste properties as you want.
'Or use: Paste:=xlAll, to paste all options.

ActiveCell.Offset(1, 0).EntireRow.Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'Add next ID to new row:
ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0).Select
Selection.Value = myNextID + 1

'Re-Hide all row that contain any form of "Closed" in Col.B!
Set myRng = ActiveSheet.Range(Cells(2, 2), Cells(ActiveSheet.Range("B65536").End(xlUp).Offset(1, 0).Row, 2))

For Each Cell In myRng
If UCase(Cell.Value) = "CLOSED" Then Cell.EntireRow.Hidden = True
Next Cell

'Select next cell.
ActiveSheet.Range("A65536").End(xlUp).Offset(0, 1).Select
Application.CutCopyMode = False
End Sub
 

Forum statistics

Threads
1,141,931
Messages
5,709,403
Members
421,635
Latest member
mehdi hannechi

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
Top