-4

I have a spreadsheet which contains a whole bunch of text and number values. I would like to have a macro search the sheet, select the rows containing the word "Total" and delete the entire row. I have been searching for hours and have not found any code that I can adapt to perform this task. I have a little experience with VBA but am out of practice and patience. The word is not case sensitive and may be contained in a string of text, for example: "Total for 12345 Jane Doe".

pnuts
  • 58,317
  • 11
  • 87
  • 139
Blazerunner1
  • 1
  • 1
  • 1
  • 2
  • 2
    Welcome to [so]. Questions here should __show research effort or attempts__. Please take a __[tour]__. – Unihedron Sep 09 '14 at 07:10

2 Answers2

1

You can try this instead:

Sub testing()
Dim pattern As String
pattern = "Total"
RowCount = ActiveSheet.UsedRange.Rows.Count
Dim i As Integer

For i = 1 To RowCount
    Dim j As Integer
    For j = 1 To 1
        If Cells(i, j) = pattern Then
           Cells(i, j).EntireRow.Delete
        End If
    Next j
Next i
End Sub

This may have more lines of code,but this will be helpful to explain your concept.

To insert it do the following in excel:

  1. press Alt+F11
  2. Rightclick Sheet1
  3. ->Insert->Module and paste this code.
Tiny
  • 27,221
  • 105
  • 339
  • 599
sabhareesh
  • 324
  • 1
  • 4
0

If you select the entire range, the following macro should delete every row that has a cell that contains "total", regardless of case.

Sub Test()
Dim cell As Range

For Each cell In Selection
    If InStr(1, cell, "total", vbTextCompare) > 0 Then
        cell.EntireRow.Delete
    End If
Next
End Sub

To insert it, do the following in Excel:

  1. Press Alt+F11
  2. Click Insert>Module
  3. Copy and paste the above macro into the new module.
Kevin McDowell
  • 532
  • 6
  • 20