Forum Discussion

SteveK14's avatar
SteveK14
Occasional Reader
Jun 03, 2026

Series fill a formula down a column automatically skipping a set number of rows

I am trying to fill a formula down a column every 6th row but incrementing the variable in the formula ($A2) for each entry. The formula uses the Take command and inputs 5 rows of data, so to keep it from "spilling" I need the formula to increment every 6 rows, having one blank row between each section. I have been able to accomplish "copying" it each 6th row with VBA #1 and filling the series with VBA #2 but can't figure out how to combine the two. Any help would be appreciated.

VBA #1:

Sub FillEvery6thCell()

Dim ws As Worksheet

Dim startRow As Long, lastRow As Long, col As String

Dim formulaText As String

Dim r As Long

 

' Set your sheet and parameters

Set ws = ThisWorkbook.Sheets("Top 5 Employees")

col = "A" ' Column to fill

startRow = 10 ' First row to start filling

lastRow = 60000 ' Last row to fill

 

' Get the formula from the starting cell

formulaText = ws.Range(col & startRow).Formula

 

' Fill every 6th cell

For r = startRow + 6 To lastRow Step 6

ws.Range(col & r).Formula = formulaText

Next r

 

Range("A2:A60000" & iRow).Replace What:="@", Replacement:="", LookAt:=xlPart, _

SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

 

MsgBox "Formula copied to every 6th cell in column " & col

End Sub

VBA #2

Sub FillFormulasDown()

Dim sourceCell As Range

Dim fillRange As Range

 

' Define the cell containing the formula

Set sourceCell = Range("A2")

' Define the target range

Set fillRange = Range("A2:A5000")

 

' Fill formulas down

sourceCell.AutoFill Destination:=fillRange, Type:=xlFillSeries

End Sub

community
excel
Formulas and Functions
Macros and VBA
Office Scripts

3 Replies

  • m_tarler's avatar
    m_tarler
    Silver Contributor
    Jun 04, 2026

    is there a reason you can't use a single spill formula to output all the rows?  something like:

    =REDUCE(TAKE(input, 5), SEQUENCE(1000), LAMBDA( p, q, VSTACK( p, TAKE(input+q, 5))))

    since I don't know what formula you are really doing it is hard to really formulate so the above is just in concept.  The same would be true for any VBA or script, it is hard to suggest when I don't understand the data structure and formula & output needed.  But in terms of VBA I would go with a variation on your first:

    ...

    ' Fill every 6th cell

    For r = startRow + 6 To lastRow Step 6

    ws.Range(col & r).Formula = [here you need to apply the change to the formula]

    Next r

    ...

    • SteveK14's avatar
      SteveK14
      Occasional Reader
      Jun 04, 2026

      My apologies, I posted before I added the formula and had to wait for it to be approved before I could reply and add it. Thank you very much for responding. I'm not familiar with some of those commands you list, but if they accomplish this, great! Basically, I need the formula to increment the variable $A2, like a flash fill, in this formula, but every 6 rows.

      =TAKE(DROP('Active EE HRODS 2026-05-21'!$A:$W, MATCH('DO Count'!$A2,'Active EE HRODS 2026-05-21'!$A$2:$A$50000, 0),0),5)

      I'm matching a building code on the 'DO Count' sheet with a list of employees assigned to that building on the 'Active EE HRODS' sheet and listing the top 5 rows for that building on a separate sheet.




      • m_tarler's avatar
        m_tarler
        Silver Contributor
        Jun 04, 2026

        here are a couple of options for you to try:

        the REDUCE-LAMBDA version:

        =IFERROR(REDUCE({"Header","row","of","data"},
                  MATCH('DO Count'!$A2:A999,'Active EE HRODS 2026-05-21'!$A$2:$A$50000, 0),
                  LAMBDA(p,q,VSTACK(p,
                                            CHOOSEROWS('Active EE HRODS 2026-05-21'!$A:$W,q+SEQUENCE(5,,0))
                                             ,""))
   ),"")

        in this case that {"Header",....} part is a 'throw away' row that can be used to create the header row of the data.  You can Probably replace that with 'Active EE HRODS 2026-05-21'!$A1:$W1 or you can wrap the whole function with a DROP(... ,1)

        The MATCH I copied from you (but would personally update it to XMATCH) with 1 difference being I used the entire range to be searched $A2:$A999  (or what ever range you need) or use a . after the colon to auto trim the range like $A2:.$A999

        Inside the LAMBDA it is stacking the found row + 4 more with a blank row to each prior found rows.  NOTE I used CHOOSEROWS with a SEQUENCE of 0,1,2,3,4 instead of TAKE(DROP(...)) but either would work.

         

        another option avoiding the LAMBDA functions is

        =IFERROR(CHOOSEROWS( VSTACK( "", 'Active EE HRODS 2026-05-21'!$A:$W),
                                            TOCOL( EXPAND( MATCH('DO Count'!$A2:.$A999,'Active EE HRODS 2026-05-21'!$A$2:$A$50000, 0)+SEQUENCE(,5),,6,1))),"")

        in this case I add a row before all the data that is 'blank' (i.e. the VSTACK in line 1)

        then I use CHOOSEROWS to select all the 'MATCH' rows and add 1,2,3,4,5 (similar to above but because of the added row I start at 1 instead of 0) in this case creating a grid of the desired row values.  I use EXPAND to add another column that are all the value 1 (i.e. the blank row I added in line 1) then use TOCOL to make it a single list of values.

         

        I did test this on a test sheet but because my test sheet didn't use the same names and ranges as you I hope my translation of those values are correct.  At the very least it gives some creative ideas on how to solve your problem without VBA