PDA

View Full Version : vba code to find row base on some conditions without using a loop or filter



s_samira_21
07-01-2015, 06:24 AM
Hi again,

i have a range of data in sheet1 like below:

Code Year City status
1 2010 A 1
2 2011 B 2
3 2015 A 1
4 2013 C 2
5 2009 B 1
6 2010 A 2
7 2001 C 1

I want to find number of the row with this conditions:

year is between 2011 T0 2014
city is C
Status is 2

I know how to find the row using For Loop or filtering range
But is there another way, like using match and index or something like that?
I want to do this in vba
what is the fastest way?

thankyou for your help

SamT
07-01-2015, 10:46 AM
Use a For Each loop with IF.. And statements


For Each Cel In Range
If (Cel.Value >= 1 And Cel.Value = 3) And Cel.Offset(0, 1) = "A" And Cel.Offset(0, 2) = "B" Then
do something
End If
Next

s_samira_21
07-01-2015, 01:43 PM
Thank you
There is a lot of data in sheet1 maybe 2000 rows. For loop is a long prosess.
Do you know a faster way?

SamT
07-01-2015, 03:12 PM
Filter on city and status then loop only thru SpecialCells xlVisible