Finding 5 consecutive empty rows

[JPL]

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

 

[Tom Ogilvy]

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

 

[Leo Heuser]

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

 

[JPL]

Thanks for these, very usefu

 

[Leo Heuser]

You're welcome.