Code for Autofill

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,690
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!
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,692
Does this do what you want?
Code:
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

Well-known Member
Joined
Nov 14, 2005
Messages
1,690
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,764
Office Version
365
Platform
Windows
How about
Code:
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

Board Regular
Joined
Mar 30, 2016
Messages
91
Try:

Code:
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
 
Last edited:

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,692
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.
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,089,745
Messages
5,410,183
Members
403,302
Latest member
Richard Genet

This Week's Hot Topics

Top