Delete row based on cell value in other sheet

Aviles

Board Regular
Joined
Dec 17, 2008
Messages
169
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.

VBA Code:
'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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
How about:

VBA Code:
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
 
Upvote 0
How about:

VBA Code:
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.
 
Upvote 0
Change this line:
.ShowAllData

for this:
VBA Code:
If .AutoFilterMode Then .AutoFilterMode = False
 
Upvote 0
Hi @DanteAmor

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

VBA Code:
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.
 
Upvote 0
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:

VBA Code:
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
 
Upvote 0
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:

VBA Code:
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)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,045
Messages
6,176,057
Members
452,701
Latest member
rfhandel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top