How to hide Rows that has no value?

Please find the attached excel, I want to hide rows that have Amount as “-”.
Can anyone help me, how to hide rows with cell reference? test.xlsx (12.2 KB)

Hi @Akash_Chavan,

There is an activity to hide and unhide row in excel. First you can iterate through the excel and have list variable created with row numbers stored which have amount as “-” and then use this activity and pass row numbers which need to hide.

1 Like

Filter DataTable Activity:
“ColumnName” = “-”
Filtermethod (rows) : Remove

Input: myDataTable
Output: myDataTable

FilterDT

Thank you for response, but not working.
Can you please check my attached project?
Please suggest. ThankHideRow.zip (43.3 KB) you.

I don’t want to remove those rows, just want to hide.

@Akash_Chavan
we can do it with the help of:

grafik

the data has some offset (does mean, the datatable ommits some excel rows) and we define a variable for this:
grafik

then following flow is calculating the row indexes which are to hide and pass this information to the balareva activity

grafik

Calculation of the indexes can be done with following:

 (From idx In Enumerable.Range(0,dtData.Rows.Count)
  Where dtData.Rows(idx)(2).toString.Trim.StartsWith("-")
  Select idx + Offset).toArray

find starter help here:
HideRows.xaml (6.5 KB)

1 Like

Thank you, but not working for me.
Can you please test the attached excel file?
I just want to hide the rows that have “0” in column “Amount”(G).
test.xlsx (14.9 KB)

yes, the implementation will not work for the last excel because of:

  • as by request it was reacting on “-”
  • Excel is different

You can modify by your self:

 (From idx In Enumerable.Range(0,dtData.Rows.Count)
  Where dtData.Rows(idx)(YourColumnNameOrIndex).toString.Trim.StartsWith(YourFilterString)
  Select idx + YourOffset).toArray

for the Offset just compare Excel and the datatable content on how many rows are ommited after read range.

Happy Automation :+1:

Hi mate,

You can do these steps based on your sample excel file:

1). Iterate through the data table .
2) Check if(Amount = “-”) then remove the row else continue.

Hope it helps ,
Thanks and regards
Sahil Garg