Delete row based on cell value in other sheet

[Aviles]

Hello,

I'm using the below code but it does not seem to work, can someone please help me correcting this?

I am trying to delete any row in sheet 'Temp' when the date in column AM does not equal the specific date in sheet 'Home' cell C4.

Thanks in advance.

'Delete row if date in column AM in 'Temp' does NOT equal date in 'Home' C4
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

        Sheets("Temp").Select

        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView

        .DisplayPageBreaks = False

        'Set the first and last row to loop through
        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

        'loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Lastrow To Firstrow Step -1

                With .Cells(Lrow, "AM")

                If Not IsError(.Value) Then

                    If .Value <> Sheets("Home").Range("C4") Then .EntireRow.Delete
                  
                End If

            End With

        Next Lrow

    End With

    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With

 

[DanteAmor]

How about:

Sub Delete_Rows_Range()
  Dim lr As Long
  
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  With Sheets("Temp")
    lr = .Range("AM" & Rows.Count).End(3).Row
    .Range("AM1:AM" & lr).AutoFilter 1, "<>" & Sheets("Home").Range("C4").Value
    .AutoFilter.Range.Offset(1).EntireRow.Delete
    .ShowAllData
  End With
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
End Sub

 

[Aviles]

How about:

Sub Delete_Rows_Range()
  Dim lr As Long
 
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  With Sheets("Temp")
    lr = .Range("AM" & Rows.Count).End(3).Row
    .Range("AM1:AM" & lr).AutoFilter 1, "<>" & Sheets("Home").Range("C4").Value
    .AutoFilter.Range.Offset(1).EntireRow.Delete
    .ShowAllData
  End With
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
End Sub

Hi @DanteAmor

Thanks for your reply. I tried your suggestion and got the following error:

Run-time error '1004':
ShowAllData method of Worksheet class failed

When I clicked 'Debug', it took me to the ".ShowAllData" part of the code

Any suggestions?

Thanks.

 

[DanteAmor]

Change this line:
.ShowAllData

for this:

If .AutoFilterMode Then .AutoFilterMode = False

 

[Aviles]

Hi @DanteAmor

Thank you for your help. In my eagerness to find a solution I stumbled on the below code which worked for me:

Dim lr As Long, i1 As Long
    lr = Range("AM" & Rows.Count).End(xlUp).Row
    For i1 = lr To 1 Step -1
    If Range("AM" & i1).Value <> Sheets("Home").Range("C4") Then Rows(i1).Delete
    Next i1

The only problem is that this code also deletes the headers in the first row which I would like to keep.

Would you know how I could fix this code to keep the headings in row 1? I believe it has to do with Offset but I'm not sure where in the code it goes.

Thanks again for your help.

 

[DanteAmor]

My macro works fine and contemplates not deleting row 1. By the way, you didn't mention you had a header. It only remained to consider removing the autofilter. try this:

Sub Delete_Rows_Range()
  Dim lr As Long
 
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  With Sheets("Temp")
    lr = .Range("AM" & Rows.Count).End(3).Row
    .Range("AM1:AM" & lr).AutoFilter 1, "<>" & Sheets("Home").Range("C4").Value
    .AutoFilter.Range.Offset(1).EntireRow.Delete
   
    If .AutoFilterMode Then .AutoFilterMode = False

  End With
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
End Sub

 

[Aviles]

My macro works fine and contemplates not deleting row 1. By the way, you didn't mention you had a header. It only remained to consider removing the autofilter. try this:

Sub Delete_Rows_Range()
  Dim lr As Long

  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  With Sheets("Temp")
    lr = .Range("AM" & Rows.Count).End(3).Row
    .Range("AM1:AM" & lr).AutoFilter 1, "<>" & Sheets("Home").Range("C4").Value
    .AutoFilter.Range.Offset(1).EntireRow.Delete
  
    If .AutoFilterMode Then .AutoFilterMode = False

  End With
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
End Sub

Thanks @DanteAmor
I tried your updated code but it ended up deleting all of the rows, except the header.
I checked the data and there are definitely values in the Temp sheet (column AM) that match the value in Home (C4)

 

[DanteAmor]

I tried your updated code but it ended up deleting all of the rows, except the header.

That's because all the cells in column AM are different from the value in cell C4 on the Home sheet.
You could put a sample of your data, use XL2BB tool look at my signature.

 

[Aviles]

That's because all the cells in column AM are different from the value in cell C4 on the Home sheet.
You could put a sample of your data, use XL2BB tool look at my signature.

You're right, I had incorrect information in the Home sheet.
All good now, appreciate your help.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.