none
Insert row with font of cell below not above RRS feed

  • Question

  • I am using this code Cells(i, 2).EntireRow.insert to insert a new row above the current cell, however the new row is automatically taking the font style of the row ABOVE it, I want it to have the same font style as the current cell.

    E.g

    Row 9 has red backcolour as this is my heading, the data on row 10 matches my vba code condition so a new row is inserted above row 10. It takes the font style from row now, I want it to keep the same style as the original row 10 which is now row 11

    Thursday, September 29, 2011 8:04 AM

Answers

  •     Cells(i, 2).EntireRow.Copy
        Cells(i, 2).EntireRow.Insert
        Application.CutCopyMode = False

    But - be careful - your formulas could not be updated coprrectly since you may be inserting above a referenced range instead of inside a referenced range. A formula like

    =SUM(E9:E11)

    will update to

    =SUM(E10:E12)

    This would probably be better if you are just below your headers:

        Cells(i, 2).EntireRow.Copy
        Cells(i +1, 2).EntireRow.Insert
        Application.CutCopyMode = False

    The same formula will update to

    =SUM(E9:E12)


    HTH, Bernie

    Thursday, September 29, 2011 3:28 PM

All replies

  •     Cells(i, 2).EntireRow.Copy
        Cells(i, 2).EntireRow.Insert
        Application.CutCopyMode = False

    But - be careful - your formulas could not be updated coprrectly since you may be inserting above a referenced range instead of inside a referenced range. A formula like

    =SUM(E9:E11)

    will update to

    =SUM(E10:E12)

    This would probably be better if you are just below your headers:

        Cells(i, 2).EntireRow.Copy
        Cells(i +1, 2).EntireRow.Insert
        Application.CutCopyMode = False

    The same formula will update to

    =SUM(E9:E12)


    HTH, Bernie

    Thursday, September 29, 2011 3:28 PM
  •     Cells(i, 2).EntireRow.Copy
        Cells(i+1 , 2).EntireRow.Insert
        Cells(i, 2).EntireRow.ClearContents
        Application.CutCopyMode = False

    Thanks Bernie, worked a treat. Just needed to update to clear the row after you insert the new one, as sometimes the new data I was putting in didnt have as many columns as the old data so some junk was getting left behind.

    • Edited by ndatkins Sunday, October 2, 2011 12:48 AM
    Saturday, October 1, 2011 11:45 PM