Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    USA - California
    Posts
    21

    VBA Function - first non-empty cell

    I would like to find the first non-empty cell (could be text or number) in a range within a row, say K5:AB5, where K5 through M5 might be empty cells and the first non-empty cell would be N5. This would span aboug 1000 rows and the first non-empty cell would occur anywhere from the first cell in the row to the almost the last cell in the row....different in each row.

    I found code for finding the last empty cell within an entire row, but this is not for the entire row, but a portion of the row.......

    Thanks in advance!

    Melinda

  2. #2
    Join Date
    Apr 2004
    Location
    Bothell, WA
    Posts
    4

    Re: VBA Function - first non-empty cell

    Hi Melinda,

    Here is some code you can try. You can set the Start Column and End Column values in it, to define the range that you want to test for an empty cell. I created a variable 'firstempty' as an array and it stores the column number of the first empty cell in the array, tied to each row number as the index (e.g. firstempty(1) = column number of the first empty cell in row one)

    I thought I was able to Dim a variable as an array, but apparently I was wrong, so I set it up as a Variant. If anyone knows what I might be doing wrong I'd appreciate the heads up.

    Anyway, here's the code I put together:

    Sub TestEmpty()

    Dim r, c As Integer
    Dim firstempty(1 To 1000) As Variant
    Dim LastRow, StartCol, EndCol As Integer

    LastRow = [A65536].End(xlUp).Row
    StartCol = 11
    EndCol = 28

    For r = 1 To LastRow
    c = StartCol

    Do While c < EndCol + 1

    If IsEmpty(Cells(r, c)) Then
    firstempty(r) = c
    MsgBox ("First Empty in Row " & r & " is column " & firstempty(r))
    c = EndCol
    End If
    c = c + 1
    Loop

    Next

    End Sub

Posting Permissions

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