Move to next cell in autofilter result

[Aldaco]

Hello,

I'm working with a .txt file openned at Excel. As information doesn't come from a database itself, I must rearrange data.
Once I have the information in Excel it looks like:

______A________ |_______B_________|
1 |Arrival time___ |__Number/Modifier _|
2 |_____________ |__456482_________|
3 |__12:30_______|__ Late___________|
4 |__24:00_______|__Missing_________|
5 |______________|__456231_________|
6 |__10:00_______|_________________|
7 |__24:00_______|__Missing__________|
8 |__12:30_______|__Late____________|
9 |______________|__321684_________|
10 |_10:00_______|_________________|
11 |_12:30_______|__Late____________|

The task is to find the number that is "Late". If Late means equal to 12:30, I thought about creating a macro that filters column A with that criteria. Then it should select cell B1 and using a function that emulates the key board's down arrow move to the next filtered result (which would be B3)

Once B3 is selected the macro can go up until it finds the first numeric result and it can assign this value to B3 (the filtered cell)

The problem is that I haven't been able to find that function that emulates the key board's arrow to move from B1 to B3 and then to B8 and B11.

I've seen that the common answer to this kind of problem is to copy filtered results to another place. Nevertheless, this solution won't work for my application because I need to use data that is out of the filtered result.

Can anyone help me?

Thanks a lot!
Regards!
Aldaco.

 

[schielrn]

You could use a loop to go down the cells and look for only visible cells:

for i = 1 to cells(rows.count,2).end(xlup).row
if cells(i,2).rowheight <> 0 then msgbox "This cells is not filtered"
next i

There are probably better ways to do what you want to do, but I don't have the time at the moment to come up with it and I'm still not exactly clear what your overall goal is. Hope that helps.

 

[Aldaco]

Thanks a lot!

It works fine!

Regards!