# 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. 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. 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].

3. 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. On 2002-07-21 05:10, beachboy wrote:

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

Dim BCell\$, NBCell\$
On Error Resume Next
On Error GoTo 0
End Sub

7. ## Re: find first blank cell in a column

Hi Guys

Found this one works OK

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

Originally Posted by gnaga
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)

' 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

#### Posting Permissions

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