Results 1 to 7 of 7

find first blank cell in a column

This is a discussion on find first blank cell in a column within the Excel Questions forums, part of the Question Forums category; Hi, First of all, thankyou to all, for your valuable replies and solutions to do things a number of different ...

  1. #1
    New Member
    Join Date
    Jul 2002
    Posts
    4

    Default

    Hi,

    First of all, thankyou to all, for your valuable replies and solutions to do things a number of different ways on my two previous posts.

    I need to be able to find the cell reference nos of the first blank cell & last non blank cell in a particular column.

    Is there a function to do this or ways to do this??

    Thanks.

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,998

    Default

    On 2002-07-20 01:20, beachboy wrote:
    Hi,

    First of all, thankyou to all, for your valuable replies and solutions to do things a number of different ways on my two previous posts.

    I need to be able to find the cell reference nos of the first blank cell & last non blank cell in a particular column.

    Is there a function to do this or ways to do this??

    Thanks.
    Let A be the column of interest.

    If A is of numeric type, use

    [1]

    =MATCH(9.99999999999999E+307,A:A)

    If A is of type text, use

    [2a]

    =MATCH("*",A:A,-1)

    unless A contains * as value; Otherwise, use

    [2b]

    =MATCH(REPT("z",50),A:A)

    unless the last cell might house a formula computed "".


    [1] (or [2]) gives you the location/position of the last non-blank/used cell in A.

    Let B1 house either [1] or [2].

    =OFFSET(A1,B1-1,0,1,1)

    will give you the value in the last used cell.

    The location/position of the first blank/empty cell can be computed with:

    =MIN(IF(LEN(OFFSET(A1,0,0,B1-1,1))=0,ROW(OFFSET(A1,0,0,B1-1,1))))

    which must be array-entered by hitting control+shift+enter at the same time, not just enter. B1, by the way, must house either [1] or [2].

    Aladin

  3. #3
    New Member
    Join Date
    Jul 2002
    Posts
    4

    Default

    Thanks, for the response Aladin.

    I was able to use them in a worksheet cell OK but when I tried to use it in a macro, it gave an error.

    Did I do something wrong or is it done a different way in a macro?

    Thanks.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,998

    Default

    On 2002-07-21 05:10, beachboy wrote:
    Thanks, for the response Aladin.

    I was able to use them in a worksheet cell OK but when I tried to use it in a macro, it gave an error.

    Did I do something wrong or is it done a different way in a macro?

    Thanks.
    For that you need someone who knows VBA.


  5. #5
    Board Regular
    Join Date
    Jul 2002
    Posts
    604

    Default

    Let A be the column for example

    Sub Find_Blank()
    Dim BCell,NBCell

    Range("A1").Select

    For I = 1 To 65536
    If ActiveCell.Value = Empty Then
    BCell = "A" & Cstr(I-1)
    NBCell = "A" & Cstr(I-2)
    Exit Sub
    Else
    Range("A" & CStr(I+1).Select
    End if
    Next I
    End Sub

    Try this code in your macro to find the blank cell & non blankcell

    Regards
    gnaga

    _________________
    With Best Regards
    GNaga
    Malaysia

    [ This Message was edited by: gnaga on 2002-07-21 07:59 ]

  6. #6

    Join Date
    Jun 2002
    Posts
    14

    Default

    On 2002-07-20 01:20, beachboy wrote:
    Hi,

    First of all, thankyou to all, for your valuable replies and solutions to do things a number of different ways on my two previous posts.

    I need to be able to find the cell reference nos of the first blank cell & last non blank cell in a particular column.

    Is there a function to do this or ways to do this??

    Thanks.

    Sub Find_Cell_Addresses()
    Dim BCell$, NBCell$
    On Error Resume Next
    BCell = Columns(1).SpecialCells(xlCellTypeBlanks)(1, 1).Address
    On Error GoTo 0
    NBCell = [A65536].End(xlUp).Address
    End Sub



  7. #7
    New Member
    Join Date
    Mar 2010
    Posts
    1

    Default Re: find first blank cell in a column

    Hi Guys

    Found this one works OK

    Have added some bits

    If you use it in more than one macro in the same work sheet, you need a unique identifier for each instance otherwise the the first instance will take precedence.

    gnaga used ' I ' in the 2nd example I've changed it to ' A ' in the first example, then to ' B ' in the second example

    Quote Originally Posted by gnaga View Post
    Let A be the column for example

    Sub Find_Blank()
    Dim BCell,NBCell

    Range("A1").Select

    For I = 1 To 65536
    If ActiveCell.Value = Empty Then
    BCell = "A" & Cstr(I-1)
    NBCell = "A" & Cstr(I-2)
    Exit Sub
    Else
    Range("A" & CStr(I+1).Select
    End if
    Next I
    End Sub
    2)

    Sub FindABlank()

    Dim BCell,NBCell

    Range("A1").Select

    For A = 1 To 65536
    If ActiveCell.Value = Empty Then
    BCell = "A" & Cstr(A-1)
    NBCell = "A" & Cstr(A-2)
    Exit Sub
    Else
    Range("A" & CStr(A+1).Select
    End if
    Next A
    End Sub

    3.

    Sub FindBBlank()

    ' Start FindFirstBlank Used A B I

    Dim BCell,NBCell

    ' Change column value

    Range("F1").Select

    ' These values can be made to nominate a specific range
    For B = 10 To 100
    If ActiveCell.Value = Empty Then
    BCell = "B" & Cstr(B-1)
    NBCell = "B" & Cstr(B-2)

    ' FoundBBlank - Your task or tasks, once the cell has been found goes here

    ActiveCell.FormulaR1C1 = "example task"

    ' End of task/s - Continue FindBBlank to it's end

    ' Remove 'Exit Sub' and change ' +1 ' to ' +0 '
    ' and it will propagate down the nominated range


    ' Exit Sub

    Else
    Range("B" & CStr(B+0).Select
    End if
    Next B

    '
    End FindBBlank

    End Sub

    -----

    Works a treat Thanks
    Benni

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com