Different Results when Running Macro Vs Stepping Into

[Goondock89]

Good Afternoon,

I've written some VBA code to insert a Vlookup, calculate, filter to a value, and delete the rows that have said value. See below.
This code comes at the beginning of a longer set. Before I execute any code I set calculations to manual.

When I step into the macro and run this it works fine. However when I run the macro the vlookup doesn't refresh and the rows i intended to delete are not deleted.
Hoping someone can help sort this out.

Thank you,

Sub Macro1()
'
' Macro1 Macro
'

'Remove Omitted Job Codes

Sheets("Raw Data").Range("CP1").Value = "Look Up"
Sheets("Raw Data").Range("CP2").Formula = "=IF(ISNA(VLOOKUP($B2,'Look Up'!$A:$B,2,0)),""No"",""Yes"")"
Sheets("Raw Data").Range("CP2").Copy
Sheets("Raw Data").Range("CO2").End(xlDown).Offset(0, 1).Activate
ActiveCell.Name = "Look_Up_End"
Sheets("Raw Data").Range("CP2", "Look_Up_End").PasteSpecial xlFormulas
Sheets("Raw Data").Calculate
On Error Resume Next
Sheets("Raw Data").Range("A1", "CP1").AutoFilter Field:=94, Criteria1:="Yes"
Sheets("Raw Data").Range("A2", "Look_Up_End").SpecialCells _
(xlCellTypeVisible).EntireRow.Delete
Sheets("Raw Data").Columns(94).EntireColumn.Delete

'
End Sub

 

[JackDanIce]

Welcome to the board, see this thread: Run doesn;t work

Make a copy of your workbook, clear all the code, replace with below and try:

Sub PepsiMax()

    Dim x   As Long: x = Sheets("Look Up").Cells(Rows.Count, 1).End(xlUp).Row
    Dim LR  As Long
 
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
 
    With Sheets("RawData")
        LR = .Range("CO" & .Rows.Count, 1).End(xlUp).Row
        .Range("CP1").Value = "Look up"
        .Range("CP2:CP" & LR).Formula = "=ISNA(VLOOKUP($B2,'Look Up'!$A$1:$B$" & x & ", 2, 0),""No"", ""Yes"")"
        .Calculate
       .Range("CP2:CP" & LR).Value = .Range("CP2:CP" & LR).Value
       On Error Resume Next
        With .Range("A1:CP" & LR)
            .AutoFilter field:=94, Criteria1:="Yes"
            .Offset(1).Resize(LR - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With
        .AutoFilterMode = False
        On Error GoTo 0
        .Range("CP:CP").Value = ""
    End With
     
     With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
 
End Sub

 

[MARK858]

Untested, what happens with the code below

Sub Macro1()
'
' Macro1 Macro
'

'Remove Omitted Job Codes

Sheets("Raw Data").Range("CP1").Value = "Look Up"
Sheets("Raw Data").Range("CP2").Formula = "=IF(ISNA(VLOOKUP($B2,'Look Up'!$A:$B,2,0)),""No"",""Yes"")"
Sheets("Raw Data").Range("CP2").Copy
Sheets("Raw Data").Range("CO2").End(xlDown).Offset(0, 1).Name = "Look_Up_End"
Sheets("Raw Data").Range("CP2", Sheets("Raw Data").Range("Look_Up_End")).PasteSpecial xlFormulas
Sheets("Raw Data").Calculate
On Error Resume Next
Sheets("Raw Data").Range("A1", "CP1").AutoFilter Field:=94, Criteria1:="Yes"
Sheets("Raw Data").Range("A2", Sheets("Raw Data").Range("Look_Up_End")).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete
Sheets("Raw Data").Columns(94).EntireColumn.Delete

'
End Sub

Btw, can you please use code tags in future as it makes your code easier to read and copy.