topic RE: Go to Visible cells after filtered in a columnn Excel in Product Forum

Go to Visible cells after filtered in a columnn Excel

Murali_MohanKon — Fri, 22 Jan 2021 15:53:00 GMT

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
------------------------------

RE: Go to Visible cells after filtered in a columnn Excel

NicholasZejdlik — Fri, 22 Jan 2021 16:52:00 GMT

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
------------------------------

RE: Go to Visible cells after filtered in a columnn Excel

Murali_MohanKon — Sat, 23 Jan 2021 05:10:00 GMT

Thanks Nicholas. It helps i will check.

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

RE: Go to Visible cells after filtered in a columnn Excel

Ben.Lyons1 — Mon, 25 Jan 2021 09:02:00 GMT

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
------------------------------

RE: Go to Visible cells after filtered in a columnn Excel

zdenek.kabatek — Mon, 25 Jan 2021 20:34:00 GMT

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
------------------------------

RE: Go to Visible cells after filtered in a columnn Excel

Murali_MohanKon — Mon, 08 Mar 2021 16:42:00 GMT

Thank you Zdeněk Kabátek.

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