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
HansVogelaar's avatar
HansVogelaar
MVP
Oct 18, 2023

TP700 

The instruction

 

Range("AB1:AB" & lastRow).Offset(1, 0).SpecialCells(xlCellTypeVisible).Areas(1).Rows(1).Select

 

selects a single cell. So there is nothing to fill down.

TP700's avatar
TP700
Copper Contributor
Oct 18, 2023
What would I need to replace this with in order to make it fill down?
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Oct 18, 2023

    TP700 

    Please explain what you want to do - it's difficult to follow the code.

    • 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