find first blank cell in a column

[beachboy]

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.

[Aladin Akyurek]

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

[beachboy]

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.

[Aladin Akyurek]

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.

[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

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 ]

[P. Tom]

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

[BenniS]

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

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