PDA

View Full Version : Solved: Copy Paste non-adjacent cells in a range



erichq
07-14-2010, 08:31 AM
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




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

p45cal
07-14-2010, 09:33 AM
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: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

erichq
07-18-2010, 07:07 AM
thanks a lot. now it works at the speed of light.cheers