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!

 

[pluce]

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

Thank you! The Header Row now stays in place, but all other data is still eliminated. It does not leave rows where the cell in column A equals the value I place in cell B1. If it is helpful, the header in row 2 for column A is "Vendor_#" with data beginning in the next row.

 

[JoeMo]

Thank you! The Header Row now stays in place, but all other data is still eliminated. It does not leave rows where the cell in column A equals the value I place in cell B1. If it is helpful, the header in row 2 for column A is "Vendor_#" with data beginning in the next row.

This code is not sensitive to the presence or absence of a header row. I have tested it and it works well for me. This suggests that the data values you see in col A as being exactly the value in B1 are actually not the exact equal. To test this pick any col A value you think is the equivalent of B1, let's say that's A10 for this example, then in any empty cell enter: =EXACT(A10,B1) and see what it returns.

 

[pluce]

This code is not sensitive to the presence or absence of a header row. I have tested it and it works well for me. This suggests that the data values you see in col A as being exactly the value in B1 are actually not the exact equal. To test this pick any col A value you think is the equivalent of B1, let's say that's A10 for this example, then in any empty cell enter: =EXACT(A10,B1) and see what it returns.

The value of A10 is 9346. I typed 9346 into cell B1 and hit enter. In empty cell F1 I typed your formula =EXACT(A10,B1) and the returned result was "TRUE". I tried it again with the value in cell A17 just to be sure. I also checked the formatting of both the cells in column A and cell B1 and both are formatted as General. I tried running the macro again with the same result, all data deleted from the table.

Just in case it is relevant, and I stated this briefly in my original post, this data is loaded automatically using a connection to an Access query. The data has not been copied and pasted into the worksheet. May not matter or may have already been taken into consideration, just trying to think of anything that might be causing an issue. Thank you!

 

[JoeMo]

The value of A10 is 9346. I typed 9346 into cell B1 and hit enter. In empty cell F1 I typed your formula =EXACT(A10,B1) and the returned result was "TRUE". I tried it again with the value in cell A17 just to be sure. I also checked the formatting of both the cells in column A and cell B1 and both are formatted as General. I tried running the macro again with the same result, all data deleted from the table.

Just in case it is relevant, and I stated this briefly in my original post, this data is loaded automatically using a connection to an Access query. The data has not been copied and pasted into the worksheet. May not matter or may have already been taken into consideration, just trying to think of anything that might be causing an issue. Thank you!

I'm always suspicious of imported data, but I'm out of ideas on this one. The only thing I can offer is if you want you can PM me and I will give you an email address where you can send your workbook and I will try to resolve the issue.

 

[pluce]

I'm always suspicious of imported data, but I'm out of ideas on this one. The only thing I can offer is if you want you can PM me and I will give you an email address where you can send your workbook and I will try to resolve the issue.

Thank you so much for all of the effort you have put in, it is greatly appreciated. I did not expect so much help so quickly.

 

[KGEORGE13]

Hello,

I am using the above code to inset cells in a column if they do not equal a specific value. Basically, just shifting cells the right amount of spaces so all cells in columns are the same. This code is working perfectly except it does not work on the first two rows after the header. I believe it has something to do with line:
For i = Rng.Cells.Count To 3 Step -1


I am not familiar with this coding. Any suggestions to get this code working on row 2?

THANK YOU