CODEWITHSUNDEEP
excelvba
KarSun
KarSun

Reputation: 15

How to automatically add text to inserted rows

Below is some code I have to automatically insert 6 lines below each pre-existing row item on a sheet. Is there a way to populate each of the 6 lines with prerendered text? The text can just be, respectively per line, 'item1', 'item2', 'item3'...

Dim lastRow As Long
lastRow = Range("B" & Rows.Count).End(xlUp).Row

Dim rowCnt As Long
For rowCnt = lastRow To 2 Step -1

Range("B" & rowCnt).Resize(6, 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove    

Next

Thanks in advance for the assistance!

Upvotes: 1

Views: 111

Answers (2)

user4039065
user4039065

Reputation:

Try adding the text as a transposed array.

dim itms as variant

itms = array("item1", "item2", "item3", "item4", "item5", "item6")

...

For rowCnt = lastRow + 1 To 2 Step -1

    Range("B" & rowCnt).Resize(6, 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove    
    Range("B" & rowCnt).Resize(6, 1) = application.transpose(itms)

Next

Upvotes: 1

SJR
SJR

Reputation: 23081

Here is one way.

Sub x()

Dim lastRow As Long

lastRow = Range("B" & Rows.Count).End(xlUp).Row

Dim rowCnt As Long, i As Long

For rowCnt = lastRow To 2 Step -1
    Range("B" & rowCnt).Resize(6, 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B" & rowCnt + 7).Resize(6).Value = Application.Transpose(Array("Item 1", "Item 2", "Item 3", "Item 4", "Item 5", "Item 6"))
Next

End Sub

Upvotes: 1

Related Questions