Code for Autofill

gheyman

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

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
Thank you both, very much. That worked
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top