Link to home
Start Free TrialLog in
Avatar of drav
dravFlag for United States of America

asked on

How do I delete a row in Excel based on the contents of a cell in that row?

I have a report that spits out a CVE file with over 10k rows and several columns. Out of those 10+k rows there are a few hundred that need to be removed. Each row that needs to be removed has a cell in which "This is informational" appears in it. When I see "This is informational" in the beginning of the cell I know I need to remove the entire row.

A potentially tricky thing is that the cell may be in one of three columns (H, I or J). And the string "This is informational" is just the beginning of a two or three sentence description.

I'm looking for an easy-button that I can have a user click that will remove any row that contains that string of characters in any cell in that row. I'm hoping Excel actually might have a built-in tool for this but I haven't located it.

I can have the file saved as xlsx if CVE isn't going to work. That's no issue.

Thanks.
Avatar of Nathan Riley
Nathan Riley
Flag of United States of America image

Try this code.
Private Sub CommandButton1_Click()
Dim r As Range, delRange As Range, searchRange As Range
Dim sh As Worksheet

Set sh = Worksheets("Sheet1")

Set searchRange = Intersect(sh.UsedRange, sh.Range("H:J"))

For Each r In searchRange
  If LCase(Left(r.Value, 21)) = "this is informational" Then
    If delRange Is Nothing Then
      Set delRange = r
    Else
      Set delRange = Union(delRange, r)
    End If
  End If
Next

delRange.EntireRow.Delete

End Sub

Open in new window

Attached is a working example
informational.xls
Or you could use this code to open the csv delete the lines and save it back. Then you could put this code in an excel sheet and run the code on any csv you want.
Private Sub CommandButton1_Click()
Dim fname As String
Dim r As Range, delRange As Range, searchRange As Range
Dim bk As Workbook, sh As Worksheet

fname = "C:\temp\informational.csv"

Set bk = Workbooks.Open(fname)

Set sh = bk.Worksheets(1)

Set searchRange = Intersect(sh.UsedRange, sh.Range("H:J"))

For Each r In searchRange
  If LCase(Left(r.Value, 21)) = "this is informational" Then
    If delRange Is Nothing Then
      Set delRange = r
    Else
      Set delRange = Union(delRange, r)
    End If
  End If
Next

delRange.EntireRow.Delete

bk.Save
bk.Close False

End Sub

Open in new window

One more. This one asks for the file when you click the button.
It also checks to make sure H:J has data before running to avoid a crash. All have been tested. Pick your favorite.
Private Sub CommandButton1_Click()
Dim fname As String
Dim r As Range, delRange As Range, searchRange As Range
Dim bk As Workbook, sh As Worksheet

Dim dlg As FileDialog
Set dlg = Application.FileDialog(msoFileDialogFilePicker)

dlg.Filters.Add "CSV files (*.csv)", "*.csv"
If dlg.Show = -1 Then
  fname = dlg.SelectedItems(1)
  
  Set bk = Workbooks.Open(fname)
  
  Set sh = bk.Worksheets(1)
  
  Set searchRange = Intersect(sh.UsedRange, sh.Range("H:J"))
  
  If Not searchRange Is Nothing Then
    For Each r In searchRange
      If LCase(Left(r.Value, 21)) = "this is informational" Then
        If delRange Is Nothing Then
          Set delRange = r
        Else
          Set delRange = Union(delRange, r)
        End If
      End If
    Next
    
    delRange.EntireRow.Delete
  End If
  bk.Save
  bk.Close False
End If
End Sub

Open in new window

Avatar of drav

ASKER

Fast responses. I like it. I will need to test this out hopefully later today maybe tomorrow morning. I will respond once I have done so. Thanks again for the fast responses.

Avatar of drav

ASKER

Still trying to get this code to work, Tommy. I keep getting errors thrown back at me. My ignorance on using vbs in Excel may be a contributing factor. It took me 10 minutes or so to figure out in Excel 2007 where I even go to input a command button and code.

I was able to get your button in and it did pop up with a window asking for me to select the csv I wanted to use. However, it throws back a Run-time error of 91 and the debugger highlights "    delRange.EntireRow.Delete"

I'm still trying to work it in. Sorry for the delayed responses.
Avatar of drav

ASKER

Gallitin, same thing I said above to Tommy can be said to your response. I'm still working on it. I can rebuild your entire datacenter, re-design your entire network, make it complaint with DOD level requirements but coding in Excel is not my strength! lol

Still working on it.
ASKER CERTIFIED SOLUTION
Avatar of TommySzalapski
TommySzalapski
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial