#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    9
    Rep Power
    0

    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
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    Cheltenham, England.
    Posts
    101
    Rep Power
    15
    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
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2001
    Location
    Dublin
    Posts
    413
    Rep Power
    14
    what versions of excel does that work for? (resetting the last cell)
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    Cheltenham, England.
    Posts
    101
    Rep Power
    15
    "what versions of excel does that work for? (resetting the last cell) "

    Definitely works in 2K (9.0.2720) Im pretty sure Ive 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 01:24 PM.

IMN logo majestic logo threadwatch logo seochat tools logo