Forum Discussion

TP700's avatar
TP700
Copper Contributor
Oct 18, 2023

Filldown VBA not working

Hello, I have some coding which filters some columns to find data I need to add a comment against, then writes the comment and fills it down to the bottom. However, the Filldown isn't working. Everyt...
Macros and VBA
TP700's avatar
TP700
Copper Contributor
Oct 18, 2023
So I want it to Enter "Completed" in the top cell (which it does) and then copy that down to the bottom, so all of those blanks filled with "Completed"
HansVogelaar's avatar
HansVogelaar
MVP
Oct 18, 2023

TP700 

Does this work?

Sub Description()
    Dim w1 As Worksheet
    Dim w2 As Worksheet
    Dim lastRow As Long
    Application.ScreenUpdating = False
    Set w1 = Worksheets("Data")
    w1.Cells.Copy
    Set w2 = Worksheets.Add(After:=w1)
    w2.Range("A1").PasteSpecial Paste:=xlPasteValues
    lastRow = w2.Cells(w2.Rows.Count, "AB").End(xlUp).Row
    Application.CutCopyMode = False
    w2.Cells.EntireColumn.AutoFit
    w2.Columns("D:D").Delete Shift:=xlToLeft
    w2.Range("O1").AutoFilter
    With w2.AutoFilter.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=Range("O1"), SortOn:=xlSortOnValues, _
            Order:=xlAscending, DataOption:=xlSortNormal
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
    w2.Range("$A$1:$BK100000").AutoFilter Field:=15, Criteria1:="Required"
    w2.Range("$A$1:$BK100000").AutoFilter Field:=28, Criteria1:=""
    w2.Range("AB2:AB" & lastRow).SpecialCells(xlCellTypeVisible).Value = "Completed"
    Application.ScreenUpdating = True
End Sub

Resources