filtering and updating

[MAMIBUSSOL]

I have the following layout

A B C D
date supplier Type Liq

21JUN A 10
21JUN B 10
19JUN C
19JUN D 10

i need to filter out the blanks in column D
update column C with text

so when I remove the filter I should end up with the following based on the above example

A B C D
date supplier type Liq

21JUN A text 10
21JUN B text 10
19JUN C
19JUN D text 10

I can perform this manually but I would rather do this with coding? Any ideas?

I have the following coding to perform the filter, however I am unsure how to do the update part

    Cells.Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$IC$46").AutoFilter Field:=8, Criteria1:="<>"

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim Sh As Worksheet
    Dim Rng As Range
    Set Sh = Worksheets("Sheet1")
    With Sh
        Set Rng = .Range("A1").CurrentRegion
        Rng.AutoFilter
        Rng.AutoFilter Field:=4, Criteria1:="<>"
        With .AutoFilter.Range.Columns(3)
            .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Value = "text"
        End With
        .AutoFilterMode = False
    End With
End Sub

 

[MAMIBUSSOL]

thanks andrew however I have just hit a snag

    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$IF$131").AutoFilter Field:=2, Criteria1:="<>"
    ActiveSheet.Range("$B$1:$IE$388").AutoFilter Field:=3, Criteria1:="="
    ActiveSheet.Range("$B$1:$IC$388").AutoFilter Field:=8, Criteria1:="<>"
 
'the above filters out the data
'the code below I thank you for
 
    With ActiveSheet.AutoFilter.Range.Columns(3)
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Value = "A"
    End With

the problem is if the filter comes back with no data which is possible, It won't allow me; correctly I might add, to update column C with the value "text". I guess what I need is to bypass this line, when required. I hope this makes sense.

 

[Andrew Poulsom]

You can just use On Error Resume Next.