Error when No data to paste
 

Hi:

in Excel 2000 spk1, I have a sub that copies a range and pastes i
somewhere else. It works fine if there is data to be pasted but if th
range is empty is error out. Is there a way to bypass the error an
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.PAST

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


thank

--
halem
-----------------------------------------------------------------------
halem2's Profile: http://www.excelforum.com/member.php...nfo&userid=993
View this thread: http://www.excelforum.com/showthread.php?threadid=49469

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

Error when No data to paste
 

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