How to auto fill column down in VBA-CodePudding

How to auto fill column down; e.g Cell(A1).value=dog and Cell(A12).value=Pen How do i fill down A2:A11 with the value=dog and A13 value=pen without manually selecting the column.

 Sub filldown_example()
    Dim missingcells as range
    Dim fillsedcells as range
    
    Set missingcells = select
    For each filledcells in missingcells
    
       If filledcells = "" Then
         filledcells.filldown
       end if
    
    next filledcells
 End sub

CodePudding user response：

You do not need VBA for this. If you search Google for excel fill all blanks with cell above you will get the Non VBA method.

If you still want VBA then try this. You do not need to loop through all cells.

Code:

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim rng As Range
    
    '~~> Change this to the relevant sheet
    Set ws = Sheet1
    
    With ws
        '~~> Find last row in Col A and add 1 to it
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row   1
           
        '~~> Find the blank cells
        On Error Resume Next
        Set rng = .Range("A1:A" & lRow).SpecialCells(xlCellTypeBlanks)
        On Error GoTo 0
        
        '~~> Add the formula to get values from the above cell in 1 go
        If Not rng Is Nothing Then rng.FormulaR1C1 = "=R[-1]C"
        
        '~~> Convert formulas to values
        .Range("A1:A" & lRow).Value = .Range("A1:A" & lRow).Value
    End With
End Sub

In Action:

CodePudding user response：

No need to loop here.

Sub fillit()

    With Range("a1:a13")
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        .Value = .Value   'formula to value
    End With

End Sub

CodePudding user response：

Fill Down Selection

This will allow you to select multiple ranges with multiple columns to fill down each of them.

Range

Sub FillDownSelectionRange()
    
    If TypeName(Selection) <> "Range" Then Exit Sub
    
    Dim rg As Range: Set rg = Selection
    
    Dim arg As Range ' Area Range
    Dim crg As Range ' Column Range
    Dim rCell As Range ' Row Cell Range
    Dim rValue As Variant
    
    For Each arg In rg.Areas
        For Each crg In arg.Columns
            If crg.Rows.Count > 1 Then
                For Each rCell In crg.Cells
                    If Len(CStr(rCell.Value)) = 0 Then
                        rCell.Value = rValue
                    Else
                        If rCell.Value <> rValue Then
                            rValue = rCell.Value
                        End If
                    End If
                Next rCell
            End If
            rValue = Empty
        Next crg
    Next arg

End Sub

Array

To speed up, instead of looping through the cells, you could loop through an array.

Sub FillDownSelectionArray()
    
    If TypeName(Selection) <> "Range" Then Exit Sub
    
    Dim rg As Range: Set rg = Selection
    
    Dim arg As Range ' Area Range
    Dim crg As Range ' Column Range
    Dim cData As Variant ' Column Array
    Dim rValue As Variant
    Dim r As Long
    
    For Each arg In rg.Areas
        For Each crg In arg.Columns
            If crg.Rows.Count > 1 Then
                cData = crg.Value
                For r = 1 To UBound(cData, 1)
                    If IsEmpty(cData(r, 1)) Then
                        cData(r, 1) = rValue
                    Else
                        If cData(r, 1) <> rValue Then
                            rValue = cData(r, 1)
                        End If
                    End If
                Next r
                crg.Value = cData
            End If
            rValue = Empty
        Next crg
    Next arg

End Sub