Forum Discussion
Please help- deleting rows containing a specific value from a large set of data
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?
- Brent_SiplCopper ContributorCan 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.
- peachypaige285Copper ContributorHow would I make a column that searches the row for it?
- manishshivale204Copper Contributor
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 BooleanSet 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 jIf flag Then
ws.Rows(i).Delete
End IfNext i
End SubBefore 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 AKindly let me know if this is what can help you. Otherwise provide more clarification on expected output or constraints.