Code for Autofill

[gheyman]

I have data from Row one down a couple of hundred rows. Its a list of parts that go into each parent part number
The report I grab the data from only puts the parent part number in column B once. So if it puts Parent "ABC123" in column B then it has 300 parts it will not list the next Parent part number until row 301

So from B3 all the way down to B299 its blank. I need code that fill do a type of autofill. I need it to take the value in B and autofilldown until the next row that is not null (But not past the last row of data).

Row E is a good one to find the range from

Any help is very much appreciated!

 

[JoeMo]

Does this do what you want?

Sub FillParent()
Dim R As Range, c As Range
Set R = Range("B:B").SpecialCells(xlCellTypeConstants, 2)
Application.ScreenUpdating = False
For Each c In R
    If c.Row >= 2 Then
        If c.End(xlDown).Row < Rows.Count Then
            c.Resize(c.End(xlDown).Row - c.Row).FillDown
        End If
    End If
Next c
Application.ScreenUpdating = True
End Sub

 

[gheyman]

Thanks JoeMo. It works but its deleting my column title in B3. I tried to modify Set R = Range("B4:B").SpecialCells(xlCellTypeConstants, 2) But it didn't like Range("B4

 

[Fluff]

How about

Sub gheyman()
   With Range("B4", Range("E" & Rows.Count).End(xlUp).Offset(, -3))
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
      .Value = .Value
   End With
End Sub

 

[Prish]

Try:

Sub Autofill()

Dim LastRow As Long
LastRow = Range("E1").End(xlDown).Row + 1


Range("B" & LastRow).Value = " "
Range("B2:B" & LastRow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"


End Sub

 

[gheyman]

Thank you both, very much. That worked

 

[JoeMo]

Thanks JoeMo. It works but its deleting my column title in B3. I tried to modify Set R = Range("B4:B").SpecialCells(xlCellTypeConstants, 2) But it didn't like Range("B4

Sorry about that, but glad you got a solution from others. For the record I think this will work for you.

Sub FillParent()
Dim R As Range, c As Range
Set R = Range("B:B").SpecialCells(xlCellTypeConstants, 2)
Application.ScreenUpdating = False
For Each c In R
    If c.Row >= 3 Then
        If c.End(xlDown).Row < Rows.Count Then
            c.Resize(c.End(xlDown).Row - c.Row).FillDown
        Else
            c.Resize(Range("E" & Rows.Count).End(xlUp).Row - c.Row + 1).FillDown
        End If
    End If
Next c
Application.ScreenUpdating = True
End Sub