PDA

View Full Version : Include Row Number in XLFilterCopy



brorick
03-31-2008, 06:51 AM
I was wondering if there is a way to copy a row number when using this line of code. I have a need to reference the row number and therefore would like to include it when the data is copied to the specific cells. Any help would be greatly appreciated. :help

A B
1 Dept EmpName
2 HR Rob
3 Acct Chrissy
4 Security Larry

If I filtered for Acct and copied the row as in the code below I would hope to include the rownumber.

Based on the code below
Z1 = Dept
Z2 = Acct

AA1 = 3 'This would be the row number
AB1 = Acct
AC1 = Chrissy


Dim rng as Range

Set rng = Range("database")

rng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"Z1:Z2"), CopyToRange:=Range("AA1:AC1"), Unique:=False


Thank you in advance.

Bob Phillips
03-31-2008, 08:16 AM
Will the filter always result in a single row, or could there be many?

brorick
03-31-2008, 10:57 AM
:thumb XLD, thank you for your response. There could possibly be many.

Bob Phillips
03-31-2008, 01:57 PM
Sub XLFilter()
Dim cell As Range

With ActiveSheet

.Columns("A:B").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=.Range("Z1:Z2"), _
CopyToRange:=.Range("AA1"), _
Unique:=False

For Each cell In Range(.Range("AA2"), .Range("AA2").End(xlDown))

cell.Offset(0, 2).Value = .Evaluate("MATCH(1,(A1:A1000=""" & cell.Value & """)*(B1:B1000=""" & cell.Offset(0, 1).Value & """),0)")
Next cell
End With

End Sub

brorick
04-01-2008, 05:52 AM
XLD thank you for your help. :clap: I will give this a try.