CODEWITHSUNDEEP
excelvba
kazSone
kazSone

Reputation: 105

Inserting blank rows in active cell visual basic excel

When I click a button, I want to add3 rows after a cell which has content. How can i improve my codes below? for the first 3 rows, there are already contents in the cells. i tried to copy the 3 rows then just erase it contents. my problem is if i already put datas in row 4 to row 6, then i click the button again, the previous row 4 to row 6 will now move down, the new blank rows will always be inserted after row 1-row 3 which is not what i want. The 1st 3 rows has a format that's why i copied it first. I want the format of the first 3 rows also to be copied but not the contents, just blank rows.

Sub newRow()
        Rows("1:3").Select
        Selection.Copy
        Selection.Insert shift:=xlDown
        Range("4:6").ClearContents
End Sub

Upvotes: 0

Views: 1522

Answers (1)

Our Man in Bananas
Our Man in Bananas

Reputation: 5981

try this:

Range("1:3").copy
range("4:6").pastespecial paste:=xlpasteformats

will copy the formats only from rows 1 to 3 to rows 4, 5 and 6

so if row 3 has a colour, then row 6 will have that colour.

If you want to make it dynamic you would need to determine the last row with content:

Activesheet.usedrange.rows.count

will return the number of used rows including rows that have formats but no content (I just tried it)

then you just plug that into your copy paste code:

Dim lngLastRow As Long

lngLastRow = ActiveSheet.UsedRange.Rows.Count
Range(lngLastRow - 2 & ":" & lngLastRow).Copy
Range(lngLastRow + 1 & ":" & lngLastRow + 3).PasteSpecial Paste:=xlPasteFormats

try that in the event handler for your button

Upvotes: 1

Related Questions