performing a find and auto filter in VBA

[Legacy 93538]

Hi

I am trying to write a macro which looks in a worksheet range C1:N1 and and finds the value in cell B1 and then in that cell put an autofilter on and filter out the blanks.

Does anyone know how to do this? As i am stuck for ideas i have considered a counta but not sure that would work. i tried doing an autofilter which filters each column in the range to filter out blanks on each column.

Thank you
Jessicaseymour

 

[JackDanIce]

Hi, try:

Sub StickingPensInNasalOrrifices()
Dim SearchRange As Range, SearchItem As Range
Dim SearchValue As Variant
Dim msg As String
Dim i As Long
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
i = Range("N" & Rows.Count).End(xlUp).Row
Set SearchRange = Range("C1:N" & i)
SearchValue = Range("B1")
msg = "Item not found"
On Error Resume Next
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
Set SearchItem = SearchRange.Find(what:=SearchValue, after:=Range("C1"), LookIn:=xlValues, LookAt:=xlWhole, _
    SearchOrder:=xlByColumns, SearchDirection:=xlNext)
 
If SearchItem Is Nothing Then
    MsgBox msg
    ActiveSheet.AutoFilterMode = False
Else
    SearchRange.AutoFilter field:=SearchItem.Column - 2, Criteria1:="<>"
End If
With Application
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
End With
 
End Sub

 

[Legacy 93538]

Hi

Thank you so much for helping works perfectly thank you!!!

 

[JackDanIce]

You're most welcome!