if single cell value is found in a range then delete entire row

Question

I am working with daily data that recorded 31 days of data per multiple stations and I need a VBA code to remove leap year days. I have a list of dates of data recorded and a list of years that are not leap years that I want to delete. To delete the extra 30 and 31 days, I used the basic following code:

Dim lastrow, i As Long

lastrow = ActiveSheet.Cells(65536, 1).End(xlUp).Row

For i = 1 To lastrow
    'delete 31st days for February
    If ActiveSheet.Range("D" & i) = 2 And ActiveSheet.Range("E" & i) = 31 Then
        Rows(i).Select
        Selection.Delete shift:=xlUp
    End If
Next i

Pretty simple and works nicely so I was hoping to be able to do something similar where I find the non-existent date (ie. 02/29/non-leapyear) within the data and delete the row but it has turned out to be extremely difficult to match a value within a range. I was thinking along the lines of something like this:

Dim lastrow, i As Long, leapyear as Workbook

Set leapyear = Workbooks("LeapYears.xlsx")

lastrow = ActiveSheet.Cells(65536, 1).End(xlUp).Row

For i = 1 To lastrow
    'obviously this is where I have the problem trying to match a cell to a range
    If ActiveSheet.Range("D" & i) = leapyear.Sheets(1)range("C2:C90") Then
        Rows(i).Select
        Selection.Delete shift:=xlUp
    End If
Next i

Any help or another way to handle this is greatly appreciated!

score 0 · Accepted Answer · edited May 23 '17 at 12:05

Modified version of my answer from This Question

Sub Sample()

Dim StartingScreenUpdateValue As Boolean
Dim StartingEventsValue As Boolean
Dim StartingCalculations As XlCalculation

With Application
    StartingScreenUpdateValue = .ScreenUpdating
    StartingEventsValue = .EnableEvents
    StartingCalculations = .Calculation
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With


Dim varTestValues As Variant

varTestValues = Workbooks("LeapYears.xlsx").Sheets(1).Range("C2:C90")

Rows(1).Insert
[A1].FormulaR1C1 = "TempHeader1"
[A1].AutoFill Destination:=Range("A1:H1"), Type:=xlFillDefault

Range("D1").AutoFilter Field:=4, Criteria1:=Application.Transpose(varTestValues), Operator:=xlFilterValues

Range("D2", Range("D" & Rows.Count).End(xlUp)) _
    .SpecialCells(xlCellTypeVisible).EntireRow.Delete

ActiveSheet.AutoFilterMode = False
Rows(1).Delete


With Application
    .ScreenUpdating = StartingScreenUpdateValue
    .EnableEvents = StartingEventsValue
    .Calculation = StartingCalculations
End With

End Sub

NOTE: This code runs assuming your data has headers if it does not please advise.

REMEMBER Always run any code on a copy of your data and not your actual data until you are confident that it is working 100%.

This code almost works but broke on the line > ActiveSheet.Range("D2",sh1.Range("D" & ActiveSheet.Rows.Count)... My data does not have headers but has the following data per workbook in columns A through H: "Station ID", "Element Type", "YYYY", "MM", "DD", "MM/DD/YYYY", "Value", "Season". The LeapYears.xlsx workbook has does have headers with "YYYY" in column A or "MM/DD/YYYY" in column C that could match the with respective columns of data workbooks to identify rows to be deleted. — lalo1551, Oct 07 '13 at 23:27
Fixed the problem line and added code to add and remove temporary headers. Let me know if it works. — user2140261, Oct 08 '13 at 12:50
Thank you for your help! I also found another quick fix - since my data uses dates, I was able to select any cell where .NumberFormat = "General" and delete that entire row as excel did not recognize it as a 'date' format since the day does not exist. Thanks again :) — lalo1551, Oct 09 '13 at 22:30

if single cell value is found in a range then delete entire row

1 Answers1

Linked