Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Find Next Available Blank Rows
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).Activat e For i = 0 To 5 ActiveCell.Offset(1, 0).Activate If ActiveCell.Value = "" Then bolEmpty(i) = True Else bolEmpty(i) = False End If Next |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Find Next Available Blank Rows
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Find Next Available Blank Rows
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. wrote: 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).Activat e 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 Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find duplicate rows then deleting them | Excel Worksheet Functions | |||
Can I delete multiple blank rows collectively? | Excel Worksheet Functions | |||
Excel find text and copy rows | Excel Discussion (Misc queries) | |||
how do I print grid lines for blank rows | Excel Discussion (Misc queries) | |||
how do I print grid lines for blank rows | Excel Discussion (Misc queries) |