Vba Delete row if data from another cell exists

[glen1987]

Hi,

I have the below code however I need to change it to reference cells C4-C4 and delete the row, if Column H contains the data typed in cells C4-C7.

Thank you for any help

.

Sub Loop_Example()
    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
    
    With ActiveSheet

  
        .Select

        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView

        'Turn off Page Breaks, we do this for speed
        .DisplayPageBreaks = False

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

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

            'We check the values in the A column in this example
            With .Cells(Lrow, "H")

                If Not IsError(.Value) Then

                    If .Value = "Data" Then .EntireRow.Delete
                    'This will delete each row with the Value "Data"                    'in Column , case sensitive.

                End If

            End With

        Next Lrow

    End With

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

End Sub

 

[Fluff]

Hi & welcome to MrExcel.
Do you have a header row & if so what row is it in?

 

[glen1987]

Hi & welcome to MrExcel.
Do you have a header row & if so what row is it in?

HI,

Yes the header row is in row 9.

Thank you

 

[Fluff]

Ok, how about

Sub Glen1987()
   Dim Ary As Variant
   
   Ary = Application.Transpose(Range("C4:C7").Value2)
   With ActiveSheet
      .Range("A9:H9").AutoFilter 8, Ary, xlFilterValues
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub

 

[glen1987]

Ok, how about

Sub Glen1987()
   Dim Ary As Variant
   
   Ary = Application.Transpose(Range("C4:C7").Value2)
   With ActiveSheet
      .Range("A9:H9").AutoFilter 8, Ary, xlFilterValues
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub

Hi Fluff, the code works perfectly when typing names etc however when I use numbers I doesn't work.
Thank you for your help

 

[Fluff]

Ok, how about

Sub Glen1987()
   Dim ary(1 To 4) As String
   Dim i As Long
   
   For i = 1 To 4
      ary(i) = Cells(i + 3, 3)
   Next i
   With ActiveSheet
      .Range("A9:H9").AutoFilter 8, ary, xlFilterValues
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub

 

[glen1987]

Ok, how about

Sub Glen1987()
   Dim ary(1 To 4) As String
   Dim i As Long
   
   For i = 1 To 4
      ary(i) = Cells(i + 3, 3)
   Next i
   With ActiveSheet
      .Range("A9:H9").AutoFilter 8, ary, xlFilterValues
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub

It works perfectly thank you for your help

 

[Fluff]

Glad to help & thanks for the feedback