Finding blank cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

teepee - 16 Feb 2008 11:48 GMT

Hello

I want to find the first and second blank cells in a column and return both
their values as variables I can then program with. Anyone know how? I got as
far as:

If ActiveSheet.UsedRange.Count < 2 Then
MsgBox 1
Else
MsgBox Columns("A:A").Find(What:="", LookAt:=xlWhole).Row
' Cells.FindNext(After:=ActiveCell).Activate
End If

which returns just the first blank cell but as a messagebox.

Any help much appreciated

Reply to this Message

Jim Cone - 16 Feb 2008 14:41 GMT

A way that works if there are actually two blank cells...
'--
Sub FirstTwoBlanksOnly()
Dim rOne As Range
Dim rTwo As Range
Dim rng As Range

Set rng = Range("A:A").SpecialCells(xlCellTypeBlanks)
Set rOne = rng.Areas(1)(1)
If rng.Areas(1).Cells.Count > 1 Then
Set rTwo = rng.Areas(1)(2)
Else
Set rTwo = rng.Areas(2)(1)
End If

MsgBox rOne.Address & vbCr & rTwo.Address
End Sub

Signature

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"teepee"
wrote in message
Hello
I want to find the first and second blank cells in a column and return both
their values as variables I can then program with. Anyone know how? I got as
far as:

If ActiveSheet.UsedRange.Count < 2 Then
MsgBox 1
Else
MsgBox Columns("A:A").Find(What:="", LookAt:=xlWhole).Row
' Cells.FindNext(After:=ActiveCell).Activate
End If

which returns just the first blank cell but as a messagebox.
Any help much appreciated

Reply to this Message

teepee - 16 Feb 2008 14:59 GMT

Many thanks Jim

Reply to this Message