find first blank cell in a column

beachboy

New Member
Joined
Jul 16, 2002
Messages
4
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.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top