Filter using assigned variable

[kamal1jacq]

Hi,

I am doing a filter using the below code:

filterRange.AutoFilter Field:=11, Criteria1:="<>"

However I do not want to filter as mentioning field as 11

Using Find I found a text "reversed" and assigned it to a variable called "celladdress"

Now cell address shows as K1

So I want to filter using this cell address (variable)

Can this be done and what will be the code.

 

[Fluff]

Try

Field:=celladdress.column

 

[kamal1jacq]

run time error '424
Object required
Showing error in the below coding

Sub assgn()


Dim Wrk1 As Workbook
Dim LCol As Long
Dim Rng As Range


'Application.ScreenUpdating = False
'Application.DisplayAlerts = False


C = Application.GetOpenFilename
Set Wrk1 = Workbooks.Open(C)

Wrk1.Activate


Sheets("Sheet1").Select
Cells.Find(What:="Reversed with", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
celladdr = Selection.Address(False, False)


MsgBox celladdr


    Dim src As Worksheet
    Dim tgt As Worksheet
    Dim trgt As Worksheet
    Dim filterRange As Range
    Dim copyRange As Range
    Dim lastRow As Long
    Dim visibleTotal As Long


    Set src = Wrk1.Sheets("Sheet1")
    Set tgt = Wrk1.Sheets("Sheet3")
  
src.AutoFilterMode = False
' find the last row with data in column A
    lastRow = src.Range("A" & src.Rows.Count).End(xlUp).Row
    
    MsgBox lastRow
    
    
  Set filterRange = src.Range("A1:Z" & lastRow)
  Set copyRange = src.Range("A1:Z" & lastRow)
    filterRange.AutoFilter Field:=celladdr.Column, Criteria1:="<>"

 

[Fluff]

In future when posting code, please use code tags (the # icon in the reply window).
try

Sub assgn()


Dim Wrk1 As Workbook
Dim LCol As Long
Dim Rng As Range
Dim Fnd As Range

'Application.ScreenUpdating = False
'Application.DisplayAlerts = False


C = Application.GetOpenFilename
Set Wrk1 = Workbooks.Open(C)

Wrk1.Activate


Sheets("Sheet1").Select
Set Fnd = Cells.find(What:="Reversed with", After:=activecell, lookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
celladdr = Fnd.Address(False, False)


MsgBox celladdr


    Dim src As Worksheet
    Dim tgt As Worksheet
    Dim trgt As Worksheet
    Dim filterRange As Range
    Dim copyRange As Range
    Dim lastRow As Long
    Dim visibleTotal As Long


    Set src = Wrk1.Sheets("Sheet1")
    Set tgt = Wrk1.Sheets("Sheet3")
  
src.AutoFilterMode = False
' find the last row with data in column A
    lastRow = src.Range("A" & src.Rows.Count).End(xlUp).Row
    
    MsgBox lastRow
    
    
  Set filterRange = src.Range("A1:Z" & lastRow)
  Set copyRange = src.Range("A1:Z" & lastRow)
    filterRange.AutoFilter Field:=Fnd.Column, Criteria1:="<>"

 

[kamal1jacq]

Fantastic, It worked. Amazing.

Thanks for your help.

By the way why do we have to use # icon when posting code.

 

[Fluff]

Glad to help & thanks for the feedback

By the way why do we have to use # icon when posting code.

It helps to preserve formatting, thereby making it easier to read.
Also a number of members will not offer assistance if you haven't used code tags, so it's in your best interest to use them