Help
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Go to Visible cells after filtered in a columnn Excel

Go to answer
Murali_MohanKon
Level 4

‎22-01-21 03:53 PM

Hi ,
need to go visible cell in a column in Excel after applying filters.

Ex: Age column filtered >25 then i need to go first visible value after filter.

Thanks
KMMPost

------------------------------
Murali Mohan Konanki Automation Associate
Automation Associate
S&P Global
UTC
8096961984
------------------------------
0 Likes
1 BEST ANSWER

Best Answers

Go to answer
NicholasZejdlik
Level 9

‎22-01-21 04:52 PM

I don't think there's a way to do this with the stock VBO. I'd check this post for an example of how to do it in VBA, which could be adapted to a code stage in Blue Prism. Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible) will return a range of visible cells, then you can use the Intersect() function to determine if a specific cell or range is contained within the range of visible cells. Since this method would not require moving the active cell around, it should be pretty quick to loop through potentially thousands of cells to see if they are visible or not.

------------------------------
Nicholas Zejdlik
RPA Developer
------------------------------

View answer in original post

0 Likes
5 REPLIES 5

Go to answer
NicholasZejdlik
Level 9

‎22-01-21 04:52 PM

I don't think there's a way to do this with the stock VBO. I'd check this post for an example of how to do it in VBA, which could be adapted to a code stage in Blue Prism. Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible) will return a range of visible cells, then you can use the Intersect() function to determine if a specific cell or range is contained within the range of visible cells. Since this method would not require moving the active cell around, it should be pretty quick to loop through potentially thousands of cells to see if they are visible or not.

------------------------------
Nicholas Zejdlik
RPA Developer
------------------------------
0 Likes

Go to answer
Murali_MohanKon
Level 4
In response to NicholasZejdlik

‎23-01-21 05:10 AM

Thanks Nicholas. It helps i will check.

------------------------------
Murali Mohan Konanki Automation Associate
Automation Associate
S&P Global
UTC
8096961984
------------------------------
0 Likes

Go to answer
BenLyons
Staff
Staff
In response to Murali_MohanKon

‎25-01-21 09:02 AM

Hi Murali,

Just in case that doesn't work out. An alternative method would be to create a Windows based object to attach to Excel, then use GSK to press the down arrow (once you've selected the header row).

Not as elegant as using VBO, but may give you simpler alternative.

Thanks

------------------------------
Ben Lyons
Product Consultant
Blue Prism
UK
------------------------------
Ben Lyons Senior Product Specialist - Decipher SS&C Blue Prism UK based

Go to answer
ZdeněkKabátek
MVP

‎25-01-21 08:34 PM

Hi, Murali,

I am attaching an action from our Excel VBO (Filter Worksheet & Get Filtered Row Numbers) which enables to set up a filter criteria (using Collection) and then returns collection with row numbers which met the filter criteria. Hopefully, it will help you. If you need more explanation send PM.

Regards,

------------------------------
Zdeněk Kabátek
Head of Professional Services
NEOOPS
http://www.neoops.com/
Europe/Prague
------------------------------
Preview file
9 KB
0 Likes

Go to answer
Murali_MohanKon
Level 4
In response to ZdeněkKabátek

‎08-03-21 04:42 PM

Thank you Zdeněk Kabátek.

------------------------------
Murali Mohan Konanki Automation Associate
Automation Associate
S&P Global
UTC
8096961984
------------------------------
0 Likes