Insert row with formats and formulas
Closed
Debs269
Posts
23
Registration date
Monday October 15, 2012
Status
Member
Last seen
August 18, 2016
-
Feb 1, 2013 at 11:32 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - Feb 5, 2013 at 12:00 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - Feb 5, 2013 at 12:00 PM
Related:
- Excel auto insert row when data changes
- Insert rows on change of value in Excel table ✓ - Excel Forum
- Transfer data from one excel worksheet to another automatically - Guide
- Excel date format dd.mm.yyyy - Guide
- Excel create unique id for each row ✓ - Excel Forum
- Nvidia drivers auto detect - Guide
2 replies
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
767
Feb 2, 2013 at 03:05 PM
Feb 2, 2013 at 03:05 PM
ClearContents will clear formulas and text both leaving format alone
Since you dont want to clear some text, you have to clear it cell by cell
Since you dont want to clear some text, you have to clear it cell by cell
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 16, 2023
544
Feb 5, 2013 at 12:00 PM
Feb 5, 2013 at 12:00 PM
Hi Debs,
Make a save first, if you are unhappy with result you can reload your file.
I assumed that if a row contains data, column A is always filled.
Here is the code:
Best regards,
Trowa
Make a save first, if you are unhappy with result you can reload your file.
I assumed that if a row contains data, column A is always filled.
Here is the code:
Sub InsertAndClearRow() Dim RowNumber As Integer RowNumber = Range("A" & Rows.Count).End(xlUp).Row + 1 Range("A5:AI5").Copy Range("A" & RowNumber).insert Shift:=xlDown For Each cell In Range("A" & RowNumber & ":AI" & RowNumber) If Left(cell.Formula, 1) <> "=" Then cell.Value = vbNullString Next cell Application.CutCopyMode = False End Sub
Best regards,
Trowa