Finding 5 consecutive empty rows
 

Hi,

Does anyone have some code for a macros that will scan down rows t
find the first cell after 5 consecutive blank cells?

Many Thanks in Advance.

JP

--
Message posted from http://www.ExcelForum.com

Finding 5 consecutive empty rows
 

Do the cells contain constants?

Dim rng as Range
Dim rng1 as Range
Dim cell as Range
On Error goto ErrHandler
set rng = Columns(1).specialCells(xlConstants)
On Error goto 0
for each cell in rng
if cell.Row 5 then
if application.CountA(cell.offset(-5,0).Resize(5,1)) = 0 then
set rng1 = cell
exit sub
end if
end if
Next
if not rng1 is nothing then
rng1.Select
End if
Exit Sub
ErrHandler:
msgbox "No cells with constants"
End Sub

--
Regards,
Tom Ogilvy


"JPL " wrote in message
...
Hi,

Does anyone have some code for a macros that will scan down rows to
find the first cell after 5 consecutive blank cells?

Many Thanks in Advance.

JPL


---
Message posted from http://www.ExcelForum.com/

Finding 5 consecutive empty rows
 

Hi JPL

Another option, assuming cells are empty and not
quasi blank (from formula):

Sub FindFirstNonEmpty()
'Leo Heuser, 28-5-2004
Dim Area As Range
Dim CheckRange As Range
Dim FirstNonEmptyCell As String
Dim NumberOfEmptyCells As Long

Set CheckRange = ActiveSheet.Columns("A")
NumberOfEmptyCells = 5

On Error Resume Next
Set CheckRange = CheckRange.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

For Each Area In CheckRange.Areas
If Area.Cells.Count = NumberOfEmptyCells Then
FirstNonEmptyCell = _
Area.Cells(NumberOfEmptyCells + 1, 1).Address
Exit For
End If
Next Area

If FirstNonEmptyCell < "" Then
MsgBox FirstNonEmptyCell
Else
MsgBox "None exist"
End If

End Sub


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"JPL " skrev i en meddelelse
...
Hi,

Does anyone have some code for a macros that will scan down rows to
find the first cell after 5 consecutive blank cells?

Many Thanks in Advance.

JPL


---
Message posted from http://www.ExcelForum.com/

Finding 5 consecutive empty rows
 

Thanks for these, very usefu

--
Message posted from http://www.ExcelForum.com

Finding 5 consecutive empty rows
 

You're welcome.


"JPL " skrev i en meddelelse
...
Thanks for these, very useful