copy cell range vba

I have a range of cells in Excel that I need to copy to th开发者_如何学Ce same sheet but in another column.

Rows 29 - 44 from column E need to be copied to column B. Simple.

BUT, the copied cells mustn't contain the value EGA.

I guess I need to iterate, but I don't know how.

How can I do so?

Try something like this:

Sub NoEGA()
    Dim vArr As Variant
    Dim j As Long

    vArr = Range("E29:E44")
    For j = 1 To UBound(vArr)
        If UCase(vArr(j, 1)) = "EGA" Then vArr(j, 1) = ""
    Next j

    Range("B29:B44") = vArr
End Sub

How about using InStr?

Sub CopyWithoutEGA()
    Dim Tests As Range
    Set Tests = Range("E29")
    Dim Output As Range
    Set Output = Range("B29")
    Dim Contents As String

    Dim nIndex As Long
    Dim nMaxIndex As Long

    nIndex = 1
    nMaxIndex = 16

    While nIndex <= nMaxIndex
        Contents = Tests.Cells(nIndex, 1)
        If (InStr(1, Contents, "EGA", 1)) Then
            Output.Cells(nIndex, 1) = "This contains EGA"
        Else
            Output.Cells(nIndex, 1) = Contents
        End If
        nIndex = nIndex + 1
    Wend
End Sub