I am trying to copy values from one sheet to another using loop with two conditions and it is very slow. Removing conditions from code doesn't have any effect on the speed of the execution. I already have sample sheet where everything is done with functions (if/match/index...) but I would like to remove all functions from this workbook.
Here is the code :
Option Explicit
Private Sub AnalitSample()
Dim WSS As Worksheet
Set WSS = Sheets("Source")
Dim WSD As Worksheet
Set WSD = Sheets("Dest")
Dim col As String
col = "B"
Dim rCell As Range
Dim rRng As Range
Set rRng = WSS.Range("B2:B4000")
Dim i As Integer
i = 2
WSD.Range("B2:G4000").ClearContents
For Each rCell In rRng.Cells
If rCell.Value <> "" Then
If Len(rCell.Value) > 3 Then
WSD.Range(col & i).Value = rCell.Offset(0, 0).Value
WSD.Range(col & i).Offset(0, 1).Value = rCell.Offset(0, 1).Value
WSD.Range(col & i).Offset(0, 2).Value = rCell.Offset(0, 4).Value
WSD.Range(col & i).Offset(0, 3).Value = rCell.Offset(0, 5).Value
WSD.Range(col & i).Offset(0, 4).Value = rCell.Offset(0, 6).Value
WSD.Range(col & i).Offset(0, 5).Value = rCell.Offset(0, 9).Value
i = i + 1
End If
End If
Next rCell
End Sub
I am asking for a few suggestions on how to speed up this kind of code (for each loop), that I was avoiding in the past because of the speed.