View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Patrick C. Simonds Patrick C. Simonds is offline
external usenet poster
 
Posts: 343
Default Search for first empty cell in column

Solved it.

"Patrick C. Simonds" wrote in message
...
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