Hello

I'm trying to find th fourth blank cell in a column and select a range based
on it. Should be simple but for some reason I can't makeit work. Anyone tell
me what I'm doing wrong? I'd be most grateful.

For background, I have some data followed by two blank cells, followed by
more data, folowed by a blank cell, followed by more data followed by
another blank cell, followed by more data followed by blank cells to the
end. It's the start of these final blanks I'm trying to find
programatically.

Problem is that after firstqun,the rest of the variables show as zero

Dim firstblank As String
Dim firstqun As String
Dim secondblank As Range
Dim secondqun As String
Dim thirdblank As Range
Dim thirdqun As String
Dim fourthblank As Range

firstblank = Columns("a:a").Find(What:="", LookAt:=xlWhole).Row
firstqun = firstblank + 2
Cells.FindNext(After:=ActiveCell).Activate


Set secondblank = Range("a" & firstqun & ":a1000").Find(What:="",
LookAt:=xlWhole)
secondqun = secondblank + 1
Cells.FindNext(After:=ActiveCell).Activate


Set thirdblank = Range("a" & secondqun & ":a1000").Find(What:="",
LookAt:=xlWhole)
thirdqun = thirdblank + 1
Cells.FindNext(After:=ActiveCell).Activate

Set fourthblank = Range("a" & thirdqun & ":a1000").Find(What:="",
LookAt:=xlWhole)
Range("A1:HH" & fourthblank).Select

"It's the start of these final blanks I'm trying to find programmatically."
According to my count that would be the fifth blank cell?

'Working from the bottom up...
Dim FifthBlank as Range
Set FifthBlank = Cells(Rows.Count, "A").End (xlUp).Offset(1,0)
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"teepee"
wrote in message
Hello
I'm trying to find th fourth blank cell in a column and select a range based
on it. Should be simple but for some reason I can't makeit work. Anyone tell
me what I'm doing wrong? I'd be most grateful.

For background, I have some data followed by two blank cells, followed by
more data, folowed by a blank cell, followed by more data followed by
another blank cell, followed by more data followed by blank cells to the
end. It's the start of these final blanks I'm trying to find
programatically.

Problem is that after firstqun,the rest of the variables show as zero

Dim firstblank As String
Dim firstqun As String
Dim secondblank As Range
Dim secondqun As String
Dim thirdblank As Range
Dim thirdqun As String
Dim fourthblank As Range

firstblank = Columns("a:a").Find(What:="", LookAt:=xlWhole).Row
firstqun = firstblank + 2
Cells.FindNext(After:=ActiveCell).Activate


Set secondblank = Range("a" & firstqun & ":a1000").Find(What:="",
LookAt:=xlWhole)
secondqun = secondblank + 1
Cells.FindNext(After:=ActiveCell).Activate


Set thirdblank = Range("a" & secondqun & ":a1000").Find(What:="",
LookAt:=xlWhole)
thirdqun = thirdblank + 1
Cells.FindNext(After:=ActiveCell).Activate

Set fourthblank = Range("a" & thirdqun & ":a1000").Find(What:="",
LookAt:=xlWhole)
Range("A1:HH" & fourthblank).Select

Dim firstblank As Range
Dim firstqun As Range
Dim secondblank As Range
Dim secondqun As Range
Dim thirdblank As Range
Dim thirdqun As Range
Dim fourthblank As Range

Set firstblank = Columns("a:a").Find(What:="", LookAt:=xlWhole)
Set firstqun = firstblank.Offset(2, 0)

Set secondblank = Columns("a:a").FindNext(after:=firstqun)
Set secondqun = secondblank.Offset(1, 0)


Set thirdblank = Columns("a:a").FindNext(after:=secondqun)
Set thirdqun = thirdblank.Offset(1, 0)

Set fourthblank = Columns("a:a").FindNext(after:=thirdqun)
Range("A1:HH" & fourthblank.Row).Select


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"teepee" wrote in message
...
Hello

I'm trying to find th fourth blank cell in a column and select a range
based on it. Should be simple but for some reason I can't makeit work.
Anyone tell me what I'm doing wrong? I'd be most grateful.

For background, I have some data followed by two blank cells, followed by
more data, folowed by a blank cell, followed by more data followed by
another blank cell, followed by more data followed by blank cells to the
end. It's the start of these final blanks I'm trying to find
programatically.

Problem is that after firstqun,the rest of the variables show as zero

Dim firstblank As String
Dim firstqun As String
Dim secondblank As Range
Dim secondqun As String
Dim thirdblank As Range
Dim thirdqun As String
Dim fourthblank As Range

firstblank = Columns("a:a").Find(What:="", LookAt:=xlWhole).Row
firstqun = firstblank + 2
Cells.FindNext(After:=ActiveCell).Activate


Set secondblank = Range("a" & firstqun & ":a1000").Find(What:="",
LookAt:=xlWhole)
secondqun = secondblank + 1
Cells.FindNext(After:=ActiveCell).Activate


Set thirdblank = Range("a" & secondqun & ":a1000").Find(What:="",
LookAt:=xlWhole)
thirdqun = thirdblank + 1
Cells.FindNext(After:=ActiveCell).Activate

Set fourthblank = Range("a" & thirdqun & ":a1000").Find(What:="",
LookAt:=xlWhole)
Range("A1:HH" & fourthblank).Select

On Sun, 27 Apr 2008 17:10:55 +0100, "teepee" wrote:

I'm trying to find th fourth blank cell in a column and select a range based
on it. Should be simple but for some reason I can't makeit work. Anyone tell
me what I'm doing wrong? I'd be most grateful.

I would approach it a bit differently, storing the blank cells in an array, and
then selecting the one you want:

======================
Option Explicit
Sub GetBlankCells()
Dim BlankCells(0 To 3) As Range
Dim rg As Range, c As Range
Dim i As Long
Set rg = Range("A:A")
Set rg = rg.SpecialCells(xlCellTypeBlanks)
i = 0
For Each c In rg
Set BlankCells(i) = c
i = i + 1
If i 3 Then Exit For
Next c
For i = 0 To 3
Debug.Print BlankCells(i).Address
Next i
End Sub
==============================

So BlankCells(3) would be the fourth empty cell.



--ron

On Sun, 27 Apr 2008 17:10:55 +0100, "teepee" wrote:

Hello

I'm trying to find th fourth blank cell in a column and select a range based
on it. Should be simple but for some reason I can't makeit work. Anyone tell
me what I'm doing wrong? I'd be most grateful.

For background, I have some data followed by two blank cells, followed by
more data, folowed by a blank cell, followed by more data followed by
another blank cell, followed by more data followed by blank cells to the
end. It's the start of these final blanks I'm trying to find
programatically.


It occurs to me you might want to be searching for cells that display a blank,
and not cells that are actually empty. If so, something like:

========================
Sub GetNullStringCells()
Dim BlankCells(0 To 3) As Range
Dim rg As Range
Dim i As Long
Set rg = Range("A:A")
Set BlankCells(0) = rg.Find("", LookIn:=xlValues)
For i = 1 To 3
Set BlankCells(i) = rg.FindNext(BlankCells(i - 1))
Next i
For i = 0 To 3
Debug.Print BlankCells(i).Address
Next i
End Sub
============================
--ron

On Sun, 27 Apr 2008 13:09:24 -0400, Ron Rosenfeld
wrote:

On Sun, 27 Apr 2008 17:10:55 +0100, "teepee" wrote:

I'm trying to find th fourth blank cell in a column and select a range based
on it. Should be simple but for some reason I can't makeit work. Anyone tell
me what I'm doing wrong? I'd be most grateful.

I would approach it a bit differently, storing the blank cells in an array, and
then selecting the one you want:

======================
Option Explicit
Sub GetBlankCells()
Dim BlankCells(0 To 3) As Range
Dim rg As Range, c As Range
Dim i As Long
Set rg = Range("A:A")
Set rg = rg.SpecialCells(xlCellTypeBlanks)
i = 0
For Each c In rg
Set BlankCells(i) = c
i = i + 1
If i 3 Then Exit For
Next c
For i = 0 To 3
Debug.Print BlankCells(i).Address
Next i
End Sub
==============================

So BlankCells(3) would be the fourth empty cell.



--ron

Rereading your original post, I'm not sure how you want to set the range after
you've found each blank cell. I'm also not sure if you want to skip the second
blank cell.
--ron

On Sun, 27 Apr 2008 13:30:03 -0400, Ron Rosenfeld
wrote:

On Sun, 27 Apr 2008 17:10:55 +0100, "teepee" wrote:

Hello

I'm trying to find th fourth blank cell in a column and select a range based
on it. Should be simple but for some reason I can't makeit work. Anyone tell
me what I'm doing wrong? I'd be most grateful.

For background, I have some data followed by two blank cells, followed by
more data, folowed by a blank cell, followed by more data followed by
another blank cell, followed by more data followed by blank cells to the
end. It's the start of these final blanks I'm trying to find
programatically.


It occurs to me you might want to be searching for cells that display a blank,
and not cells that are actually empty. If so, something like:

========================
Sub GetNullStringCells()
Dim BlankCells(0 To 3) As Range
Dim rg As Range
Dim i As Long
Set rg = Range("A:A")
Set BlankCells(0) = rg.Find("", LookIn:=xlValues)
For i = 1 To 3
Set BlankCells(i) = rg.FindNext(BlankCells(i - 1))
Next i
For i = 0 To 3
Debug.Print BlankCells(i).Address
Next i
End Sub
============================
--ron


Rereading your original post, I'm not sure how you want to set the range after
you've found each blank cell. I'm also not sure if you want to skip the second
blank cell.
--ron

Many thanks Bob and Ron. I'm in your debt.