CODEWITHSUNDEEP
excelvba
billy218
billy218

Reputation: 3

Fill down a formula in VBA only where there is data in the column before

apologies as I am new here. I've done a lot of searching and am failing to find answers and I am losing my hair. I have data that is broken up in blocks of rows. Here's an example:

Group1
August 4
Sam
Bob
Rosie

Group 2
March 13
Carl
Jon

I want to insert a column and add a formula in that column everywhere there is data in the first column. The length and placement of code blocks vary. Any help or tips would be greatly appreciated!

Here's what I have so far but it only fills down the first block:

    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B8").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(TRIM(RC[-1]),participants.xlsx!R2C1:R1000C4,3,FALSE)"
    Range("B8").Select
    Selection.AutoFill Destination:=Range(Range("B8"), Range("A8").End(xlDown).Offset(0, 1)) ```

Upvotes: 0

Views: 89

Answers (1)

SJR
SJR

Reputation: 23081

You can loop through the areas (this code assumes the cells do not contain formulas):

Sub x()

Dim r As Range

For Each r In Columns(1).SpecialCells(xlCellTypeConstants).Areas
    r.Offset(, 1).Insert shift:=xlToRight
    r.Offset(, 1).Formula = "=row()+1"
Next r

End Sub

Starting data is orange, the code adds the green.

enter image description here

Editted to insert a column. You could just a column at the beginning once if that meets your needs.

Upvotes: 1

Related Questions