VBA Find Next Available Blank Rows

[robin.coe]

I'm trying to write a piece of VBA that will search a worksheet for 5
consecutive blank rows in order to paste data there. While I've
written code that does this, however, while the code is searching for
the 5 consecutive blank rows it is changing the value in some cells to
the number 1 and I can't figure out why. Here's the portion of the
code that's doing this - it happens on the
ActiveCell=Application.WorksheetFunction.Find("", a8:a75") line. Any
help is appreciated.

Worksheets("Fees").Range("a8").Activate
Do
ActiveCell = Application.WorksheetFunction.Find("",
"a8:a75")
addrStartingCell = ActiveCell.Address
Worksheets("Fees").Range(addrStartingCell).Activate

For i = 0 To 5
ActiveCell.Offset(1, 0).Activate
If ActiveCell.Value = "" Then
bolEmpty(i) = True
Else
bolEmpty(i) = False
End If
Next

 

[Dave O]

I was getting a "1" result too- I wonder if that's some kind of boolean
result.

Here's some code to find the first empty cell in the a8:a75 range. If
I can suggest, try replacing this line
ActiveCell = Application.WorksheetFunction.Find("", "a8:a75")

with these:
Range("A8:A75").Select
Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

 

[Dave Peterson]

Another way to find empty cells in a range is to select the range and then
Edit|goto|special|blanks.

This limits the selection to just the empty cells in that range.

In code, it would look something like:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myOrigRng As Range
Dim myCell As Range
Dim HowMany As Long
Dim DestCell As Range
Dim SomeRng As Range

HowMany = 5

With ActiveSheet
Set SomeRng = .Range("a1:a5") 'something to copy

Set myOrigRng = .Range("a8:a75")
Set myRng = Nothing
On Error Resume Next
Set myRng = myOrigRng.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
End With

If myRng Is Nothing Then
MsgBox "No empty cells in the original range: " _
& myOrigRng.Address(0, 0)
Else
Set DestCell = Nothing
For Each myCell In myRng.Cells
If Application.CountA(myCell.Resize(HowMany, 1)) = 0 Then
Set DestCell = myCell
Exit For
End If
Next myCell

If DestCell Is Nothing Then
MsgBox HowMany & " consecutive empty cells not available"
Else
If Intersect(DestCell.Resize(HowMany, 1), myOrigRng).Address _
<> DestCell.Resize(HowMany, 1).Address Then
MsgBox "Found a blank cell at: " & DestCell.Address(0, 0) _
& vbLf & "but not enough empty cells under it"
Else
SomeRng.Copy _
Destination:=DestCell
MsgBox "Pasted into: " _
& DestCell.Resize(HowMany, 1).Address(0, 0)
End If
End If
End If
End Sub

This actually looks for 5 consecutive empty cells in column A--not 5 consecutive
rows.