CODEWITHSUNDEEP
excelvba
Kai-Chun Lin
Kai-Chun Lin

Reputation: 363

selection of multiple columns with end(xlDown).End(xlUp).Row

I'm doing debug for a VBA template. The Macro let users choose a excel file to import, then it will check each columns whether they correpond the requirements. When I try to import a file, I alway get error "oveeflow" with the following line:

'maxRow = ws.Range(Cells(StartRow, AdditionalInfoColumns.OtherInfo).Address).End(xlDown).Row

My colleague tells me that I just need to add end(xlDown).End(xlup) like this:

maxRow = ws.Range(Cells(StartRow, AdditionalInfoColumns.OtherInfo).Address).End(xlDown).End(xlDown).End(xlUp).Row

And it does work! I don't know why. So I try to do it in a simpler way as following: 

maxRow = ws.Range(Cells(StartRow, 1).Address, Cells(StartRow, 3).Address).End(xlDown).End(xlUp).Row

It works as well. Can someone please explain why does it work? Thank you for your answer. PS: The value of xlDown and xlUp are -4121 and -4162.

Best Regards Kai

Upvotes: 0

Views: 3479

Answers (2)

Pᴇʜ
Pᴇʜ

Reputation: 57733

First of all End(xlDown) and End(xlUp) is nothing more than …

  • End(xlDown) = Pressing Ctrl + Arrow down
  • End(xlUp) = Pressing Ctrl + Arrow up

you can test this manually by selecting a range/cell and press the combination to see the effect on your actual worksheet.

The overflow error just comes because if you eg. select cell A1 in an empty worksheet and do .End(xlDown) it selects the very last cell of the worksheet. And if you now insert something it fails because the maximum rows of Excel are exceeded.

To find the last used row of a column it's better to use End(xlUp) instead of End(xlDown) eg:

LastUsedRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
'finds the last used row in column A

Upvotes: 2

Harassed Dad
Harassed Dad

Reputation: 4704

End(xldown) has the same effect as pressing control and the down arrow (and End(xlright) is control and right arrow. If you try those key presses in a cell you will see it jumps to the next intersection of filled and empty cells (or the edge of the spreadsheet if there's no filled cells in the way)

Upvotes: 0

Related Questions