Finding blank cells

  • Thread starter Thread starter teepee
  • Start date Start date
T

teepee

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
 
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
--
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
 
Back
Top