Solved: Copy Paste non-adjacent cells in a range

[erichq]

Hi,
I am looking for a way to copy&paste special a range of cell, which is not adjacent.

The Sub below does exactly, what I want to achieve, but works extremely slow. Is there a way to speed that up?

Cheers,
erichq



[vba]
Sub Fix_Bloomberg(Blatt_name)

Application.ScreenUpdating = False


Dim Zelle As Range
Dim FoundRange As Range

Blatt_name.Select
Set FoundRange = Find_Range("bd", Range("A1:CZ1000"))

If Not FoundRange Is Nothing Then
For Each Zelle In FoundRange
Zelle.Copy
Zelle.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Next Zelle

End If



Application.ScreenUpdating = True
End Sub

[/vba]

[p45cal]

It may depend on how fast Find_RTange is.
I've made some assumptions in the code below, so see if it works as you want:[vba]Sub Fix_Bloomberg(Blatt_name)
Dim myArea As Range
Dim FoundRange As Range
Application.ScreenUpdating = False

Set FoundRange = Find_Range("bd", Blatt_name.Range("A1:CZ1000"))

If Not FoundRange Is Nothing Then
For Each myArea In FoundRange.Areas
myArea.Value = myArea.Value
Next myArea
End If
Application.ScreenUpdating = True
End Sub
[/vba]

[erichq]

thanks a lot. now it works at the speed of light.cheers