Error when No data to paste

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

Or maybe this if Bk5 is the only cell with something in it...

Option Explicit
Sub testme()

Dim RngToCopy As Range
Dim CellToPaste As Range
Dim NonBlankCells As Long

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

NonBlankCells = Application.CountA(RngToCopy)

If NonBlankCells = 0 Then
'nothing in it
Else
Set RngToCopy = RngToCopy.Resize(NonBlankCells, 1)
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