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 figu...
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.
