Working with excel first/last data row

[Stephanie_Rimel (Rimel, Stephanie)]

Hi everyone! So I ran into a small problem, this is my first automation project so I am still getting a feel for things. This is the use case:
I am iterating through selects, submit, which then returns query results in the form of a data table, I extract the data table and then I need to append this data table in an excel file. The excel sheet is determined by the country that the user selects. So I have an excel file with a sheet that has the headers, and then I copy this sheet and name it after the country that the user selects which is represented by the var: country. The robot checks to see if the sheet already exists and then creates it with no difficulty.

The problem arises when I am trying to find the first last data row…there is a top row with all the headers in my duplicated excel sheet like this:

but it cannot find the first last data row with the country passed as the variable.

It always returns that the first and last data row is -1 so it never is able to append the extracted data table in the excel file. I have tried to append the data this way:

(and without the quotes around country)
and received this output:

The data has no problem being written to the data table when I use the range on a sheet that ALREADY exists, but not when the sheet is referenced by the country variable and has no data in it. How do I solve this?

This works:

This does not work, but this is the way I need to use it, by passing the “United States” as a variable, var: country.

Thank you so much for your time and your patience!

[Rahul_Unnikrishnan]

I hope you are trying to write to the Free cell in the Country column? Is that correct?

Then you need to append the Datatable to the last available call in the Excel. But here the “What to Write” seems wrong…Could you recheck that part and in the destination you will have to give the last free cell value.

[ushu (Usha kiranmai)]

@Stephanie_Rimel When you are using Find First/Last Data row activity does it giving -1 even the sheet exists ?

[Stephanie_Rimel (Rimel, Stephanie)]

but the what to write: "Excel.Sheet[“United States”].Range[_FilterDatabase] works. It only stops working when I pass the string “United States” as a varaible

I need united states to be a variable, but it doesnt work. I need to figure out why

[Stephanie_Rimel (Rimel, Stephanie)]

I got it, I needed to use the custom input and not the _FilterDatabase as the range like this, and I also had to do an offset for the first and last data row like this:

First row offset and last row offset is 2 if you have a header I guess.