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.

Thanks
Andy

Hi,

I do something similar to find the bottom row, as follows: -

<snip>
Dim objBottomRow As Object
Dim intBottomRow As Integer

Sheets("sheet-name").Select
Set objBottomRow = Worksheets("sheet-name").Cells.SpecialCells(xlCellTypeLastCell)

intBottomRow = objBottomRow.Row
</snip>

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").

HTH

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).