Forum Discussion

peachypaige285's avatar
peachypaige285
Copper Contributor
Sep 13, 2024

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_Sipl's avatar
    Brent_Sipl
    Copper Contributor
    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.
  • 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.

Resources