Default AutoFill & xlCellTypeBlanks (VBA)

[xldope]

Please help!

I have a column in a worksheet that has data filled in sections with blank cells in between. I want to create a macro that will find a section of blank cells and perform a default autofill (xlFillDefault) based on the value in the cell above each blank section. Then repeat down to the end of the worksheet.

I've seen plenty of examples for filling blank cells with the cell above, but not for performing a default autofill.

 

[My Aswer Is This]

Try this:
This is assuming we are dealing with Column 'A"

Sub Test_3()
Application.ScreenUpdating = False
Dim i As Integer
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To Lastrow
        If Cells(i, 1).Value = "" Then
            Cells(i, 1).Value = Cells(i - 1, 1).Value
        End If
    Next
Application.ScreenUpdating = True
End Sub

 

[MARK858]

That is probably because there isn't a default autofill what is your issue with filling blank cells with the cell above (edit: as per post #2)?

 

[xldope]

Try this:
This is assuming we are dealing with Column 'A"

Sub Test_3()
Application.ScreenUpdating = False
Dim i As Integer
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To Lastrow
        If Cells(i, 1).Value = "" Then
            Cells(i, 1).Value = Cells(i - 1, 1).Value
        End If
    Next
Application.ScreenUpdating = True
End Sub

Sorry, the data is in column "B". The code updated blanks in column A which wasn't needed.

 

[xldope]

That is probably because there isn't a default autofill what is your issue with filling blank cells with the cell above (edit: as per post #2)?

I want the values to be sequential not the same value repeated.

 

[JoeMo]

This applies to Col A - change to suit. Is this what you want?

Sub DefaultAutoFill()
'Change column to suit
Dim Ar As Range, R As Range
Application.ScreenUpdating = False
With Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    On Error Resume Next
    Set R = .SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0
    If R Is Nothing Then Exit Sub
    For Each Ar In R.Areas
        Ar(1).Offset(-1, 0).AutoFill Destination:=Ar(1).Offset(-1, 0).Resize(Ar.Cells.Count + 1, 1), Type:=xlFillDefault
    Next Ar
End With
Application.ScreenUpdating = True
End Sub

 

[xldope]

Perfect! Just saved me a whole lot of mouse time.

Thanks JoeMo

 

[JoeMo]

Perfect! Just saved me a whole lot of mouse time.

Thanks JoeMo

You are welcome - thanks for the reply.