Excel Filter and CurrentIndex out of Sync

Help Studio
1

Hi All,
Here’s my scenario:

I’m using Filter from the Modern Excel Activities to filter a column for a certain value and then I use a For Each Excel Row activity and an If Condition to check the following:

String.IsNullOrEmpty(CurrentRow(“ColumnName”).ToString)

Then I’m trying to highlight the empty cells in each current row using Format Cells for column “G” + CurrentIndex.ToString.

The problem is since I have a filter on my data the row numbering is off and random (Ex. Rows go 1, 4, 5, 8, etc.) and my highlighting thus is thrown off.

Is there a way to account for a filter when using CurrentIndex and CurrentRow to fix this issue?

Thanks in advance

2

Hi @related-robot

Can you please provide sample input and expected output

Regards,

3

@related-robot
After filter use Copy paste Range activity.It copies only the data which is unhide,and in the new sheet perform your operations

4

@rlgandu Is there any way to use the same sheet because I need to run 3 different filters separately?

5

@lrtetala

By sample input do you mean an excel file?

6

@related-robot

Yes, If the data is confidential please share sample data

7

@related-robot
Can you Please provide the input and output sample

in my thought before filter only you have perfom the task you want,please specify why you apply filter

8

@related-robot if you give any details on requirement will provide the solution .

10

CurrentRow.FirstCell will give you A + whatever the row is. So then you can just replace the A with G.

11

@postwick what would that look like?

Would it be CurrentRow.SeventhCell?

12

I’m trying to see how I can replace A with G because when I try CurrentRow.FirstCell + 6 I get an error

13

No, it would be…

Replace(CurrentRow.FirstCell,"A","G")

CurrentRow.FirstCell + 6

CurrentRow.FirstCell is not a number. It’s a string like “A1” “A37” etc

14

I’m getting an error that says Replace is a class type and cannot be used as an expression when I try this:

Replace(CurrentRow.FirstCell,“A”,“G”)

15

CurrentRow.FirstCell.Replace("A","G")

16

@related-robot

One simple way would be instead of loop…filter on empty values also…then inside loop you can just pass currentrow.Address to get rhe current row address feomwhich you can extract the row number if needed as well and color

CurrentRow.Address will give you the address of the current item inside for eqch row in excel

Currentrow.FullRangeName also will give the range

Cheers

17

Thanks @postwick, your method works for me.

18

@Anil_G

I’m not sure I understand. Could you please elaborate?

19

@related-robot

You can apply filter for blank values also and then use format cells inside instead of if condition inside loop…this reduces the number of items further less and no if condition is needed

Cheers