Please help- deleting rows containing a specific value from a large set of data

[peachypaige285]

Hi, I'm doing a project for a class at my university and need to clean up some data. For this data, any missing data is put in as "-9999". I need to delete all rows that contain -9999, but can't figure out how to do it. It's an insane amount of data, there's over 100,000 rows and the columns go up to HE, so it's not reasonable to do manually. Is there a function that can help me do this?

[#2]

Can you create a new column/field that searches the rest of the row for "-9999"? If so you could use that to filter it out. Then if sorting is permitted you could sort by that column, select all rows, and delete them.

[#3]

How would I make a column that searches the row for it?

[#4]

@peachypaige285 

As variant

in general depends on which Excel platform/version you are

[#5]

In the new column, first cell, start with one cell to search for the criteria. I don't know if you are expecting an exact match or searching for that as text within a string. For exact match it's just
=if(A2=-9999,"match","no match")
Otherwise you need to use the FIND function.
If you are looking in multiple columns for that text, I can only figure out how to use one column to test per column of data.
=IF(FIND("-9999",A2)>0,"found","not found")
In my formula if it's not found, then you get #VALUE, but you could use it.

[#6]

@peachypaige285 

I simulated vba script on my sample data of 100 columns and it worked deleting rows with value -9999.

Sub DeleteRowsWithMissingData_Loop_AllColumns()
Dim ws As Worksheet
Dim LastRow As Long
Dim LastCol As Long
Dim i As Long, j As Long
Dim flag As Boolean

Set ws = ActiveSheet
LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
LastCol =ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

' Start with last row when deleting'
For i = LastRow To Step -1
flag = False
' From column A to HE, loop to check if it contains -9999'
For j = 1 To 100
If ws.Cells(i,j).Value = -9999 Then
flag = True
Exit For
End If
Next j

If flag Then
ws.Rows(i).Delete
End If

Next i
End Sub

Before running script, make sure to have check on duplicate sheet.

Otherwise, yes Manually. Insert column left (Name it FLAG) to Column B and add this formula
=ISNUMBER(MATCH(-9999,B2:HE2,0)
Now Add Filter and then select TRUE only.
Go To Cell A2 in filtered rows, Ctrl +shift + Down
Right click-> Delete Row
Clear Filter
Delete Column A

Kindly let me know if this is what can help you. Otherwise provide more clarification on expected output or constraints.