Delete row based on cell value in other sheet

Aviles

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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
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
 

Aviles

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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
Change this line:
.ShowAllData

for this:
VBA Code:
If .AutoFilterMode Then .AutoFilterMode = False
 

Aviles

Board Regular
Joined
Dec 17, 2008
Messages
155

ADVERTISEMENT

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.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
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
 

Aviles

Board Regular
Joined
Dec 17, 2008
Messages
155

ADVERTISEMENT

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)
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
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

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

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,330
Messages
5,635,647
Members
416,870
Latest member
rikimon2

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
Top