Question Excel : Find last row with data compare specific columns


New member
Jun 2, 2013
Programming Experience
Hello every one

i have a problem i can't solve it which is

i need to compare specific columns which one is tall than the other
= find last row with data >> but not last row in all sheet i need last row in specific columns
that is my proplem

i created a project i have attached it with a test excel file

if i use

Dim xlworksheetTolastRow As Long = xlworksheetTo.Cells(xlworksheetTo.Rows.Count, ColumnValue).End(Excel.XlDirection.xlUp).Row

it can detect the right last row if the sheet has not format
but if i try it with sheet that is formatted it will get the last row that has format not data

so i tried this one ( UsedRange )

Dim xlworksheetTolastRow As Long = xlworksheetTo.Cells(xlworksheetTo.UsedRange.Rows.Count, ColumnValue).End(Excel.XlDirection.xlUp).Row

it is working good with sheets that has formatted

if i use it with normal sheet not format when i try to compare two columns which one is taller = has last row data

sometimes (some rows) it can't detect that the row has data
for example : columns E with F or E only or F only

i do not understand why this happens > what i am doing wrong

Is there any other way to compare specific columns which one has last row (data only) and works with both sheets formatted and not format ???

i hope someone can help me with that


  • Testing Excel last
    25.5 KB · Views: 32
Last edited by a moderator:


VB.NET Forum Moderator
Staff member
Dec 17, 2005
Programming Experience
You could use UsedRange, but notice that in unformatted sheet it is A3:G15 and Rows.Count=13, so you must get last cell:
Dim maxrow = sheet.UsedRange.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row '= 15
Since last value in any column could be in that row you must start with a cell in one row higher before using "End(Up)" function.
Top Bottom