Code for Autofill

gheyman

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

MrExcel MVP
Joined
May 26, 2009
Messages
16,645
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,678
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
30,523
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,645
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
 

Forum statistics

Threads
1,078,437
Messages
5,340,277
Members
399,361
Latest member
Linford

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top