Question Excel : Find last row with data compare specific columns

[Ell0ll]

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

 

[JohnH]

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.