Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jun 2002
    Location
    Toronto, Ontario
    Posts
    45
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Find empty cell (VB & Excel 2000)

    ARRGH! I've been looking for hours (it seems), I can't remember and can't seem to find
    how to find the first empty cell in a spreadsheet.
    This is the kludge that I have been using

    For daRow = 790 To 4000
    With Worksheets("Ravenshoe").Cells(daRow, daCol)
    If .Value = "" Then Exit For
    End With
    Next daRow

    'MarkOne - Top for Drawing box
    MarkOne = daRow
    I use the value of MarkOne to identify the start location to enter some formatting.
    Then i fill in data from a number of textboxes. I think I read somewhere about
    xlDown and xltop which would allow you to determine where the last cell of data
    in a column is, but I also need to be able to fefer to that location to add the formatting.

    Any help would be much appreciated
    Thanks,
    John

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find empty cell (VB & Excel 2000)

    You can use the following to get the row number of the last use row in column A:A:

    <pre>Dim lLAstRow As Long
    lLastRow = Worksheets("Sheet1").Range("A65536").End(xlUp).Row
    </pre>

    Legare Coleman

  3. #3
    Lounger
    Join Date
    Jun 2002
    Location
    Toronto, Ontario
    Posts
    45
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Find empty cell (VB & Excel 2000)

    Thanks, a lot!

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Find empty cell (VB & Excel 2000)

    ActiveSheet.UsedRange.Rows also works unless you have leading blank rows. See finding last filled cell for more info than you want. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find empty cell (VB & Excel 2000)

    I think that you meant ActiveSheet.UsedRange.Rows.Count

    If you want to use UsedRange, the following works even if you do have empty rows at the top:

    <pre>Dim IlLastRow As Long
    IlLastRow = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1
    </pre>

    Legare Coleman

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Find empty cell (VB & Excel 2000)

    There's also:

    Range(<range>).SpecialCells(xlLastCell).Select

    SpecialCells methods are often useful for working with already completed data, which isn't what you are asking to do in this case.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •