Testing for the last column in the spreadsheet
I require to search through a row of a spreadsheet picking out values to populate a combobox. Each value is seperated by 4 or 5 empty cells. My problem is, to perform the test of the while loop, how do I know when I have reached the last column? ie when there are no value after that.
I do something similar to find the bottom row, as follows: -
Dim objBottomRow As Object
Dim intBottomRow As Integer
Set objBottomRow = Worksheets("sheet-name").Cells.SpecialCells(xlCellTypeLastCell)
intBottomRow = objBottomRow.Row
Just did a quick hack and "objBottomRow.Column" will (I think) give what you need (you'll need to translate the number returned to "A", "B", etc.).
(Note that to ensure Excel resets the value of the last cell, you need to save the workbook with the current selection as "A1").
what versions of excel does that work for? (resetting the last cell)
"what versions of excel does that work for? (resetting the last cell) "
Definitely works in 2K (9.0.2720) – I’m pretty sure I’ve also used it in ’97.
Just tested it (in 2K) and it appears you no longer have to be positioned in A1 before doing the save (perhaps that was only necessary in ’97).
Last edited by NicMic; July 5th, 2003 at 02:24 PM.