Code for Autofill

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,742
Office Version
  1. 365
Platform
  1. Windows
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 date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,199
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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,742
Office Version
  1. 365
Platform
  1. Windows
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
46,209
Office Version
  1. 365
Platform
  1. 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

ADVERTISEMENT

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:

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,742
Office Version
  1. 365
Platform
  1. Windows
Thank you both, very much. That worked
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,199
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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,108,810
Messages
5,525,017
Members
409,615
Latest member
papaluigi94

This Week's Hot Topics

Top