Question Excel : Find last row with data compare specific columns

Ell0ll

New member
Joined
Jun 2, 2013
Messages
1
Programming Experience
Beginner
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


VB.NET:
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 )


VB.NET:
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
 

Attachments

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

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,439
Location
Norway
Programming Experience
10+
You could use UsedRange, but notice that in unformatted sheet it is A3:G15 and Rows.Count=13, so you must get last cell:
VB.NET:
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