Fill static text into cell every X amount of rows

Question

I have code that fills in a blank row every X amount of records. What I want to do is have some code that will then plug into a cell in those empty rows some static text. Here is what I have for the adding of blank rows... code not all mine I nabbed it from the internet. What I need it to do is to fill in static text into the blank line it is creating. Then continue on padding and adding every 50 records. Thanks!

**********
Sub InsertRowEveryXrows()

   Dim rw As Long
   Dim lr As Long
   Dim cnt As Long
   lr = Range("A" & Rows.Count).End(xlUp).Row
   rw = 2
   cnt = 1
   Do
    If cnt = 50 Then
       Rows(rw).Insert Shift:=xlDown
       cnt = 1

    Else
       cnt = cnt + 1
    End If
    rw = rw + 1
   Loop While rw <> lr
End Sub
*****************

score 0 · Answer 1 · answered Jul 26 '16 at 18:58

The only thing you have to do it's ask when cnt make it to 51 and add your static text (assuming column it's 1), like this :

 Sub InsertRowEveryXrows()

   Dim rw As Long
   Dim lr As Long
   Dim cnt As Long
   lr = Range("A" & Rows.Count).End(xlUp).Row
   rw = 2
   cnt = 1
   Do
     If cnt = 50 Then
         Rows(rw).Insert Shift:=xlDown
         cnt = 1
     Else
        if cnt = 51 then
          cells(rw,1) = "your static text"
        else
         cnt = cnt + 1
        End If
     End If
     rw = rw + 1
   Loop While rw <> lr

 End Sub

Tell me how it goes, hope it helps !

score 0 · Answer 2 · answered Jul 26 '16 at 19:35

I added comments in the code to show what was happening but this worked for me and should be a bit quicker.

Public Sub Sample()
Dim WkSht   As Worksheet
Dim LngLR   As Long 'Last Row
Dim LngCR   As Long 'Current Row

'Connect to the worksheet
Set WkSht = ThisWorkbook.Worksheets("Sheet1")

    'Get the last row so we know when to stop
    LngLR = WkSht.Range("A" & WkSht.Rows.Count).End(xlUp).Row

    LngCR = 51 '51 to account for the first row being a header

    'Keep adding the 50th row until when would end up past the last row
    Do Until LngCR > LngLR

        'Add the new row
        WkSht.Rows(LngCR).Insert Shift:=xlDown

        'Populate it
        WkSht.Range("A" & LngCR) = "Your static text"

        'Increase the last row as it will now be one more
        LngLR = LngLR + 1

        'Got to the next 50th
        LngCR = LngCR + 50
    Loop

Set WkSht = Nothing

End Sub

I connected to the Worksheet as a variable as this is good practice, what your doing works fine for now but it can be confusing to read and if the code gets bigger ambiguity make cause things to happen you didn't expect.
I incremented 50 at a time instead of 1, technically this make it 50 times faster, but it is all a blur on todays computers :)
Your code didn't account for the last row moving down one each time you added a row.

Fill static text into cell every X amount of rows

2 Answers2