paste new over existing data without pasting blanks?

  • Thread starter Thread starter judoist
  • Start date Start date
J

judoist

I have a database with columns of numbers. I also have data from anothe
source that has numbers but with blank spaces in between.

Is it possible to paste the new data over the existing so that th
contents of the original cells can be viewed at the points where th
new data had blank spaces. Rather like putting a table cloth with hole
over another talbe cloth.

Is this possible
 
This might work for you:

Option Explicit
Sub testme01()

Dim origCell As Range
Dim destCell As Range
Dim origRng As Range

Set origRng = Nothing
On Error Resume Next
Set origRng = Application.InputBox(prompt:="Range to Copy", Type:=8)
On Error GoTo 0

If origRng Is Nothing Then
Exit Sub 'user cancelled.
End If

'only one column and no multiple areas!
Set origRng = origRng.Areas(1).Columns(1)

Set destCell = Nothing
On Error Resume Next
Set destCell = Application.InputBox(prompt:="Top Cell to Paste", _
Type:=8).Cells(1)
On Error GoTo 0

If destCell Is Nothing Then
Exit Sub 'they cancelled here
End If

For Each origCell In origRng.Cells
If IsEmpty(origCell) Then
'do nothing
Else
origCell.Copy _
Destination:=destCell
End If
Set destCell = destCell.Offset(1, 0)
Next origCell

End Sub
 
Back
Top