How to Get tha last row numer in a Excel file

Help
1

Hi.

I don’t know how to get the last row number which has data in a Excel file. Like below sample ,vacant rows are included .
Without typing any function into the file.It’s because typing any data into Excel is not allowed by my client.

I can’t attach file ,so typed below ↓

hogehogehogehoge(Cell A1)
→ vacant rows
→ vacant rows
name name name name name name name name (beguin row 4)
aaa aaa aaa aaa aaa aaa aaa aaa
→ vacant rows
→ vacant rows
aaa aaa aaa aaa aaa aaa aaa aaa
aaa aaa aaa aaa aaa aaa aaa aaa
aaa aaa aaa aaa aaa aaa aaa aaa
aaa aaa aaa aaa aaa aaa aaa aaa
aaa aaa aaa aaa aaa aaa aaa aaa
aaa aaa aaa aaa aaa aaa aaa aaa
aaa aaa aaa aaa aaa aaa aaa aaa
aaa aaa aaa aaa aaa aaa aaa aaa
aaa aaa aaa aaa aaa aaa aaa aaa
aaa aaa aaa aaa aaa aaa aaa aaa
aaa aaa aaa aaa aaa aaa aaa aaa
aaa aaa aaa aaa aaa aaa aaa aaa
aaa aaa aaa aaa aaa aaa aaa aaa
aaa aaa aaa aaa aaa aaa aaa aaa
aaa aaa aaa aaa aaa aaa aaa aaa

aaa aaa aaa aaa aaa aaa aaa aaa
aaa aaa aaa aaa aaa aaa aaa aaa
aaa aaa aaa aaa aaa aaa aaa aaa (The last row)
↑ I want to get the last row number

3 Likes
2

Hi,

Read Range activity will let you store all this data into a table which then you can get the count and last row. From my understanding, if you use “” as your range it will take the first row with data so if there are blank rows at the start, it will skip them. However, yours is starting on the first row so that shouldn’t be a problem.

To get the last row in this case, you can use .Rows.Count
So if dt is your datatable variable stored from using the Read Range with range of “”,
dt.Rows.Count

You can verify the correct value with a Message Box or Write Line

If for some reason the vacant rows are not included, try using a complete range in the Read Range like “A1:H1000”

Hope that answers your question.

Thanks.

1 Like
3

Thanks ClaytonM

I tried
1.Read Range with range of “” (output dt)
2.assign inLastRow = dt.Rows.Count
3.Write Line inLastRow.Tostring

But 1 minused number was showed.
When the Excel sheet has 10 rows,inLastRow showed 9
When I checked AddHeaders excuting Read Range , inLastRow showed 8 (even minus1)

4

I think you can get by Marco ,too :grinning:

5

HI messitao

What is Marco ?

6

Hi
Macro is develop by VBA , you can google what is vba
and
endRowIndex=Sheets(“Sheet1”).Range(“B65525”).End(xlUp).Row

1 Like
7

Hi
You wrote Ma”r”co so I couldn’t understand.
It’s Ma"c"ro,OK, Thanks.

1 Like
8

Hi oitot,

Dt.rows.count +1( add header is unchecked)
Dt.rows.count+2( add headers is checked)

Assign this to any int variable and you are done…
:relaxed:

2 Likes
9

Just for your understanding. In Datatable index starts from 0 and in excel it starts from 1.
Now if header of excel are added then. Datable index 0 will be equal to excel row 2.
And if header is not added then Datable index 0 will be equal to excel row 1.

4 Likes
10

As index of excel sheet and data table are started with 1 and 0 respectively. This will need to read last row data from table.rows.count - 1 (since start index is 0) .

PDI Steps to achieve

  1. Use Read Range Activity
    set input: SheetName=“your sheet”, Range=“” (this must be empty to read all value from sheet),
    set output: yourDataTable
    AddHeaders: Checked (First row of excel sheet become header for yourDataTable)
  2. Use Assign Activity
    rCount (this is your row counter)= yourDataTable.Rows.Count
  3. Write Line Activity
    yourDataTable.Rows(rcount-1)(0).ToString (this write First cell value of last row in output pan)
1 Like
11

In Datatable index starts from 0 ,Thanks !

1 Like
12

When I used yourDataTable.Rows(rcount-1)(0).ToString in 3.Write Line Activity, no number or letter showed.
anyway thanks for your answering.

1 Like
13

@oitot,
If it showed no number or letter and did not show an error, then the last row has nothing in the first column. You might need to verify that there’s data there, and also look at what rcount-1 is because that will tell you which row it is looking at.

Thanks.

1 Like
14

Thanks ClaytonM.

1 Like