VBA Delete row if value in column A does not equal value in cell B2

[pluce]

Good Morning,

I have been searching for hours for the answer to this question. I have ZERO experience with VBA, but am trying to make an idea come to life. I have an excel sheet which pulls a bunch of data from an Access Query. I want the user of this spreadsheet, specifically tab/worksheet titled "Download Data" to which the query is linked, to be able to enter a value in cell B2 and then click the "Run Query" button to filter the data. I want the macro behind that button to search every cell in column A which contains data and compare it to the value which was entered in cell B2. When the cell in column A does not equal the value which was placed in cell B2, I want it to delete that entire Row. I then want it to search for and delete duplicate values in Column E. So far here is what I have come up with in my research for the first step of this process:

Sub Button1_Click()
For i = .Range("A" & Rows.Count).End(x1Up).Row To 3 Step -1
If Range("A" & i).Value <> Cells(1, 2) Then Rows(i).Delete shift:=x1Up
Next i
End Sub

The data in Red is highlighted in my VBA editor, so I'm assuming my error is there. Like I said, I no experience so I don't know what any of this stuff means, or where to input data which pertains to my spreadsheet specifically vs generic information that applies to all spreadsheets. My data starts in row 3. Row 1 contains my button and the cell which will be used to input the filter data. Row 2 is just column headings. As of right now, there are over 500,000 rows with information, but this fluctuates so I need an open-ended range of rows to check, starting with row 3.

Hopefully this all makes sense. Please feel free to roll your eyes at me and call me a moron, I'm sure there are many people who will be able to write out this code in a matter of seconds but it is just flying right over my head. Thank you so much for your help!

 

[wideboydixon]

Remove the "." before Range

Also, Cells(1, 2) is B1 not B2 so check that it's right.

WBD

 

[pluce]

Thank you for the quick response! I removed the "." before Range and double checked, B1 is actually the cell I need so I just wrote it wrong in my explanation. Thank you for catching. I am still getting the following:

Run-time error '1004':

Application-defined or object-defined error

And then when I click "Debug" it shows me the second line highlighted:

For i = Range("A" & Rows.Count).End(x1Up).Row To 3 Step -1

Here is what the debugger tells me when I click on each part of that line:

For i: "i = Empty"

Range: "Range("A" & Rows.Count.End(... = Application-defined or operation-defineded error<application-defined or="" operation-defined="" error=""><application-defined or="" object-defined="" error="">

Rows.Count: "Rows.Count = 1048576"

(x1Up): "x1Up = Empty"

If gives the same message as it did for Range if I click on any other part of that line...</application-defined></application-defined>

 

[Fishboy]

Thank you for the quick response! I removed the "." before Range and double checked, B1 is actually the cell I need so I just wrote it wrong in my explanation. Thank you for catching. I am still getting the following:

Run-time error '1004':

Application-defined or object-defined error

And then when I click "Debug" it shows me the second line highlighted:

For i = Range("A" & Rows.Count).End(x1Up).Row To 3 Step -1

Here is what the debugger tells me when I click on each part of that line:

For i: "i = Empty"

Range: "Range("A" & Rows.Count.End(... = Application-defined or operation-defineded error<application-defined or="" operation-defined="" error=""><application-defined or="" object-defined="" error="">

Rows.Count: "Rows.Count = 1048576"

(x1Up): "x1Up = Empty"

If gives the same message as it did for Range if I click on any other part of that line...</application-defined></application-defined>

Hi Pluce,

Try the following variation of what your original code is doing:

Sub DeleteRows()
Dim i As Long, LastRow As Long, Rng As Range


LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Range("A1:A" & LastRow)


For i = Rng.Cells.Count To 3 Step -1
    With Rng.Cells(i)
        If .Value <> Range("B1").Value Then
            .EntireRow.Delete
        End If
    End With
Next i


End Sub

 

[pluce]

Thank you!

I copied and pasted this code in. I didn't receive any error messages back, but the spreadsheet has been running the macro for over an hour now...still waiting for the filtered results. Any thoughts on how long this filtering should take? Thank you!

 

[JoeMo]

Thank you!

I copied and pasted this code in. I didn't receive any error messages back, but the spreadsheet has been running the macro for over an hour now...still waiting for the filtered results. Any thoughts on how long this filtering should take? Thank you!

See if this works for you - and should be much faster.

Sub DeleteRows()
Dim i As Long, LastRow As Long, Rng As Range, myVal

myVal = Range("B1").Value
LastRow = Cells(Rows.Count, "A").End(xlUp).ROW
Set Rng = Range("A1:A" & LastRow)
Application.ScreenUpdating = False
Columns("B").Insert
With Range("B3:B" & LastRow)
    .Formula = "=IF(A3=" & myVal & ","""",1)"
    .Value = .Value
End With
With Range("A2:B" & LastRow)
    .AutoFilter field:=2, Criteria1:=1
    On Error Resume Next
    .SpecialCells(xlCellTypeVisible).EntireRow.Delete
    On Error GoTo 0
End With
Columns("B").Delete
Application.ScreenUpdating = True
End Sub

 

[pluce]

See if this works for you - and should be much faster.

Sub DeleteRows()
Dim i As Long, LastRow As Long, Rng As Range, myVal

myVal = Range("B1").Value
LastRow = Cells(Rows.Count, "A").End(xlUp).ROW
Set Rng = Range("A1:A" & LastRow)
Application.ScreenUpdating = False
Columns("B").Insert
With Range("B3:B" & LastRow)
    .Formula = "=IF(A3=" & myVal & ","""",1)"
    .Value = .Value
End With
With Range("A2:B" & LastRow)
    .AutoFilter field:=2, Criteria1:=1
    On Error Resume Next
    .SpecialCells(xlCellTypeVisible).EntireRow.Delete
    On Error GoTo 0
End With
Columns("B").Delete
Application.ScreenUpdating = True
End Sub

Thank you, everyone, for your assistance! I replaced with the above code, and while it does run quicker, the outcome is that the macro returns no data. The worksheet ends up completely blank from row 2 on, so my header line and everything else just disappears. I am entering a value into cell B1 which I can see in column A, so I know it is a valid value, and I have tried several of them resulting in the same outcome each time.

I need all rows where the cell in Column A = the value I typed in cell B1 to remain intact, and the header row (row 2) to remain intact. I need any row where the cell in column A does not match the value I typed in cell B1 to be deleted.

Thank you so much!

 

[JoeMo]

Thank you, everyone, for your assistance! I replaced with the above code, and while it does run quicker, the outcome is that the macro returns no data. The worksheet ends up completely blank from row 2 on, so my header line and everything else just disappears. I am entering a value into cell B1 which I can see in column A, so I know it is a valid value, and I have tried several of them resulting in the same outcome each time.

I need all rows where the cell in Column A = the value I typed in cell B1 to remain intact, and the header row (row 2) to remain intact. I need any row where the cell in column A does not match the value I typed in cell B1 to be deleted.

Thank you so much!

I think you are not getting the expected results because you have not told us what your data layout looks like. Your headers in row 2 cover how many columns? Is the first header in col A?

 

[pluce]

That may be the case! Here is the layout of my spreadsheet:

10 columns (A - J)
Row 1 Contains the following: A1 = text "Filter Parameter", B1 = "enter value", C1 = button which will be clicked to run the macro
Row 2 Contains only column headers/identifiers for all 10 columns. No relevant data to be sorted. Should remain as is.
Data begins in row three and extends down currently to row 525207 but this will fluctuate so the range needs to be from row 3 down to whatever row happens to be the last row with data.

Please let me know if there is any other specific information that is necessary to know. Thank you!

 

[JoeMo]

I think you are not getting the expected results because you have not told us what your data layout looks like. Your headers in row 2 cover how many columns? Is the first header in col A?

Here's a modification that doesn't depend on knowledge of the presence and location of a header row.

Sub DeleteRows()
Dim i As Long, LastRow As Long, myVal

myVal = Range("B1").Value
LastRow = Cells(Rows.Count, "A").End(xlUp).ROW
Application.ScreenUpdating = False
Columns("B").Insert
With Range("B3:B" & LastRow)
    .Formula = "=IF(A3=" & myVal & ",1,"""")"
    .Value = .Value
    On Error Resume Next
    .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
Columns("B").Delete
Application.ScreenUpdating = True
End Sub