Table of Contents
In order to insert a new row in VBA with formatting applied, the following steps can be followed:
1. Define the range where the new row needs to be inserted using the “Range” function.
2. Use the “Insert” method to insert a new row in the defined range.
3. Use the “EntireRow” property to select the newly inserted row.
4. Apply the desired formatting using the “Format” or “Style” functions.
5. Finally, use the “Insert” method again to insert the formatted row into the original worksheet.
Following these steps will allow the insertion of a new row with applied formatting in VBA.
VBA: Insert Row with Formatting
You can use the following syntax in VBA to insert a row that has the same formatting as the row above it:
Sub insertRowWithFormatting()
ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrAbove
ActiveCell.EntireRow.Copy
ActiveCell.Offset(1).EntireRow.PasteSpecial xlPasteFormats
Application.CutCopyMode = FalseEnd Sub
This particular macro will insert one row below the currently selected cell with the exact same formatting as the cells in the currently selected row.
Note: The line Application.CutCopyMode = False specifies that the cut and copy mode should be turned off after running the macro.
The following example shows how to use this syntax in practice.
Example: Insert Row with Formatting in VBA
Suppose we have the following dataset in Excel that contains information about various basketball players:
Suppose we would like to insert a row below row 2 with the exact same formatting.
We can create the following macro to do so:
Sub insertRowWithFormatting()
ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrAbove
ActiveCell.EntireRow.Copy
ActiveCell.Offset(1).EntireRow.PasteSpecial xlPasteFormats
Application.CutCopyMode = FalseEnd SubWe can then select cell A2 and then run this macro.
When we run this macro, we receive the following output:
Notice that the new row we inserted has the exact same formatting as the cells in row 2 including the cell colors and the border.
Also notice that all other rows in the existing dataset were simply pushed down.
Cite this article
stats writer (2024). How can I insert a new row in VBA with formatting applied?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-insert-a-new-row-in-vba-with-formatting-applied/
stats writer. "How can I insert a new row in VBA with formatting applied?." PSYCHOLOGICAL SCALES, 24 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-insert-a-new-row-in-vba-with-formatting-applied/.
stats writer. "How can I insert a new row in VBA with formatting applied?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-insert-a-new-row-in-vba-with-formatting-applied/.
stats writer (2024) 'How can I insert a new row in VBA with formatting applied?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-insert-a-new-row-in-vba-with-formatting-applied/.
[1] stats writer, "How can I insert a new row in VBA with formatting applied?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I insert a new row in VBA with formatting applied?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.