Adding a row

[Dan in Lowell]

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?

 

[Joe Was]

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]

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]

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]

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