Trying to find the fourth blank cell in a column
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
|