Search for first empty cell in column

[Patrick C. Simonds]

The code below is intended to paste data onto a worksheet by selecting the
first empty cell in row B. My problem is that I can not be sure what
column/row the User will have selected when they run the macro.

Is there any way to alter the code below so that it will always start it's
search with cell B6 and then go to the first empty cell below that in column
B?



Sub Paste_Data()

Dim BCell, NBCell
Dim PasteTo As Range
Dim rng
Set rng = Cells(ActiveCell.Row, 1)

Application.ScreenUpdating = False
Application.EnableEvents = False

For i = 1 To 65536
If ActiveCell.Value = Empty Then
BCell = "B" & CStr(i - 1)
NBCell = "B" & CStr(i - 2)

GoTo Finished

Else
Range("B" & CStr(i + 1)).Select
End If
Next i


Finished:

rng(1, 2).Select

ActiveSheet.Paste

Application.EnableEvents = True

End Sub

 

[Patrick C. Simonds]

Solved it.

 

[Matthew Herbert]

Patrick,

See the code below as an alternative method. You'll need to update the code
to fit your needs (i.e. the copy range and paste destination range in
particular).

As a side note to your code. You can change the numbers in your For Next
loop (e.g. i = 7 to Rows.Count) to start the loop in a specified location.
Keep in mind though that your BCell = "B" & CStr(i - 1) will evaluate to B0
on the first loop with i = 1 (of course, if the IF statement evaluates True).
Since there is no B0 in the spreasheet, this will throw an error if you try
to do something like Range(BCell).Select. The same is true for NBCell = "B"
& CStr(i - 2). Until you reach i = 3 in your loop, this line will throw an
error if you try to do something like Range(NBCell).Select. You don't need
the GoTo statement either. Replace the GoTo line with Exit For (i.e. once
the condition is true, exit the loop).

Best,

Matthew Herbert

Sub PasteData()
Dim rngSearch As Range
Dim rngFound As Range
Dim rngCopy As Range

'set the range to search for the first blank cell
Set rngSearch = Range("B6", "B" & Rows.Count)

'return the first cell of all the blank cells
Set rngFound = rngSearch.SpecialCells(xlCellTypeBlanks)(1)

If rngFound Is Nothing Then
MsgBox "No blank cells in " & rngSearch.Address & "."
Exit Sub
Else
'copy a cell and paste it
'CHANGE THIS TO BE THE DESIRED CELL TO COPY
Set rngCopy = Range("A1")

'CHANGE THIS TO BE THE DESIRED CELL DESTINATION
rngCopy.Copy rngFound.Offset(1, 0)
End If

End Sub

 

[Don Guillett]

Looping is NOT the way to do this
if you are looking for the first blank in col b from the top
fbr=cells(activecell.row,2).end(xldown).row+1
if you want to go from the bottom up as is usual
fbr=cells(rows.count,2).end(xlup).row+1
if you want to do from the active column just change ,2 to
,activecell.column