CODEWITHSUNDEEP
vbaexcel
CC268
CC268

Reputation: 203

Get rid of .Select in VBA Code

I would like to get rid of the sht2.Select and sht2.Range("B2").Select in the code below. Is there a way to do this?

Sub Remaining()

Dim sht2 As Worksheet    
Dim cell As Range

Set sht2 = ThisWorkbook.Worksheets("Sheet2")

sht2.Select
sht2.Range("B2").Select    
With sht2
    For Each cell In .Range("B2", Cells(Rows.Count, "B").End(xlUp))
        If .Range("A:A").Find(What:=cell.Value2, LookAt:=xlWhole) Is Nothing Then
           Intersect(.UsedRange, cell.EntireRow).Offset(, 1).Copy Sheets("Sheet1").Cells(Rows.Count, "L").End(xlUp).Offset(1)
           cell.Interior.Color = vbYellow
       End If
    Next cell
End With

End Sub

Upvotes: 2

Views: 282

Answers (1)

Tim Williams
Tim Williams

Reputation: 166316

Sub Remaining()

    Dim sht2 As Worksheet
    Dim cell As Range

    Set sht2 = ThisWorkbook.Worksheets("Sheet2")

    With sht2
        'A few fixes in the following line to make sure everything
        '   is referencing the correct sheet
        For Each cell In .Range(.Range("B2"), .Cells(.Rows.Count, "B").End(xlUp))
            If .Range("A:A").Find(What:=cell.Value2, LookAt:=xlWhole) Is Nothing Then
               Intersect(.UsedRange, cell.EntireRow).Offset(, 1).Copy _
                      Sheets("Sheet1").Cells(Rows.Count, "L").End(xlUp).Offset(1)
               cell.Interior.Color = vbYellow
           End If
        Next cell
    End With

End Sub

Upvotes: 6

Related Questions