abssorb
New Member
- Joined
- Apr 15, 2008
- Messages
- 34
- Office Version
- 365
- 2019
- 2016
- 2013
- 2011
- 2010
- Platform
- Windows
I have a worksheet with about 30 cols of data user by others. 20% of the cols are actual input data, the rest are formulae, lookups etc.
When a new row is inserted, it's important that the formulae are copied down, so I've added a macro to try and achieve this. I'm not great with VBA, so a crude macro inserts a new row below the current one, copies current to next one down, and then deletes the copied content.
However this is unsafe. If an autofilter is applied for instance the wrong row gets selected and gets deleted. How can I be sure to select the next actual row, and not the one that autofilter thinks is next?
Or maybe there's a better way altogether? Guidance welcome.
When a new row is inserted, it's important that the formulae are copied down, so I've added a macro to try and achieve this. I'm not great with VBA, so a crude macro inserts a new row below the current one, copies current to next one down, and then deletes the copied content.
However this is unsafe. If an autofilter is applied for instance the wrong row gets selected and gets deleted. How can I be sure to select the next actual row, and not the one that autofilter thinks is next?
Or maybe there's a better way altogether? Guidance welcome.
VBA Code:
Dim myCell
Set myCell = ActiveCell
' Use current selected cell, or selected row, insert a new row BELOW it and copy down
Rows(ActiveCell.Row).Select
Selection.Copy
Selection.Insert Shift:=xlDown
' For the row just created, select any text or number (exclude formulae) and clear the contents.
On Error GoTo HandleBlanks
Rows(ActiveCell.Row + 1).SpecialCells(xlCellTypeConstants).Select
Exit Sub
Selection.ClearContents
HandleBlanks:
' This is here for error trapping. In case user runs the same macro twice, which means it will run on an empty row with
' nothing to copy, which will cause 1004 Error: No cells were found
Exit Sub
End Sub