Select visible cells in one column after autofilter

[kdorian]

What I'm trying to do seems like it should be simple, but at this point I've tried so many things my head is swimming, and nothing is working right.

I need to select the visible cells in column J after autofiltering (on a different column) and set their value. I don't even care at this point if the header gets overwritten, as long as the code is simple.

 

[Domenic]

Here's an example that assumes that Sheet1 contains the data, and which does the following...

1) Filters Column A for "x".

2) If any records are found, a value is entered in each of the visible cells in Column J. Otherwise, a message box is displayed to notify the user that no records were found.

3) Clears the filter.

Here's the code. You'll need to make the necessary changes to suit your needs.

Option Explicit

Sub test()

    Dim lastRow
    With Worksheets("Sheet1")
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
  
    Dim filterRange As Range
    Dim filterArea As Range
    Dim currentCell As Range
    With Worksheets("Sheet1").Range("A1:J" & lastRow)
        .AutoFilter field:=1, Criteria1:="x" 'change the criteria accordingly
        On Error Resume Next
        Set filterRange = .Offset(1, 9).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not filterRange Is Nothing Then
            For Each filterArea In filterRange.Areas
                For Each currentCell In filterArea
                    currentCell.Value = "NewValue" 'change the value accordingly
                Next currentCell
            Next filterArea
        Else
            MsgBox "No records found!", vbExclamation
        End If
        .AutoFilter 'clear the filter
    End With
  
End Sub

Hope this helps!

 

[kdorian]

Very helpful! Thank you so much, you're a lifesaver!

 

[Domenic]

You're very welcome, cheers!