Maybe something like:

Option Explicit
Sub testme()

Dim RngToCopy As Range
Dim CellToPaste As Range

With ActiveSheet
Set RngToCopy = .Range("Bk5", .Range("bk5").End(xlDown))
Set CellToPaste = .Range("a5")

Do
If CellToPaste.Value = "" Then
Exit Do
Else
Set CellToPaste = CellToPaste.Offset(1, 0)
End If
Loop
End With

If Application.CountA(RngToCopy) = 0 Then
'nothing in it
Else
RngToCopy.Cut _
Destination:=CellToPaste
End If

End Sub




halem2 wrote:

Hi:

in Excel 2000 spk1, I have a sub that copies a range and pastes it
somewhere else. It works fine if there is data to be pasted but if the
range is empty is error out. Is there a way to bypass the error and
maybe have a msgbox come up and adivise the user?

this is a portion of my rookie code:

Range("BK5").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Cut
'
Range("A5").Select
Do
If ActiveCell.Select < "" Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell = ""
'
ACTIVESHEET.PASTE

When it goes to ActiveSheet.Paste, if it there's no data to paste it
errors out.

thanks

--
halem2
------------------------------------------------------------------------
halem2's Profile: http://www.excelforum.com/member.php...fo&userid=9930
View this thread: http://www.excelforum.com/showthread...hreadid=494694

--

Dave Peterson