Auto-filter and select row

[CatherineLavoie]

Hi!

I've been trying to create a macro for a specif need for 3 days, and can't find how...
Hope someone could help me here!


I would like to find and select the row in the column F of Sheet2 (called "Table1" in the macro) that contains the value of the cell F2 of the Sheet1.
Since there's a lot of data in the the column F of Sheet2, I'm using the auto-filter.

Here what I've done:


Range("F2").Select
Selection.Copy
Sheets("Sheet1").Select
Range("B2").Select
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=5, Criteria1:= _
Sheets("Sheet2").Range("F2"), Operator:=xlAnd
ActiveCell.Select


The macro does find the cell. But it did not select the cell - which is the most important because I need the row number! I want the "result cell" to be selected - I don't want to have a message box telling me the row number.

Could someone help me on this?

Thanks so much!

 

[SkipVought]

hi,

Do you actually need the cell Selected. or do you need the row number? I AVOID using the activate & select methods.

dim sFind as string, rFound as range
sFind = Range("F2").Value
with Sheets("Sheet1")
  Set rFound = .Rows(5).find(sFind)
  if not rFound is nothing then
     'found the value
     msgbox "the row is " & rFound.row
  Else
     'NOT FOUND
  End if
end with

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[CatherineLavoie]

Thanks for the quick answer!
But as I said, I would like to have the row selected instead of having a message box. Is it possible?
Thanks!

 

[PHV]

Replace this:
msgbox "the row is " & rFound.row
with this:
rFound.EntireRow.Select

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

dim sFind as string, rFound as range
sFind = Range("F2").Value
with Sheets("Sheet1")
  Set rFound = .Rows(5).find(sFind)
  if not rFound is nothing then
     'found the value
     .activate
     .rFound.entirerow.select[b][highlight]
'now what?  Is that what you need to SEE, or do you need to DO something with this ROW???[/highlight][/b]
  Else
     'NOT FOUND
  End if
end with

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}