-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
  • 56,678
  • 9
  • 81
  • 133
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
  • 26,031
  • 99
  • 315
  • 583
sabhareesh
  • 314
  • 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
  • 443
  • 5
  • 18