Text Functions‎ > ‎

Delete Rows

This page is dedicated to special search functions for deleting rows.


Delete Rows On All Sheets by String Search

PROBLEM:
"I have a spreadsheet that has multiple tabs of data, I would like a macro that would either ask for a code or use the one that is selected which would then run through all the sheets and delete the rows where that data is shown?"

SPECIFICATIONS:
  1. Popup message will require user to enter the value to search for
  2. Popup message will default value on the current cell selection value
  3. Popup will inquire as to whether WHOLE-CELL only matches are required.  If whole cell is NOT required, then rows with "catch" and "scat" would be deleted during a search for "cat"
  4. Macro will cycle through all sheets

CODE

Option Explicit

Sub DeleteOnAllSheets()
'Author:    Jerry Beaucaire
'Date:      5/18/2011
'Summary:   Delete all rows in all sheets that have a specific string
Dim ws          As Worksheet
Dim MatchWhole  As Long
Dim MyStr       As String
Dim strFIND     As Range

MyStr = Application.InputBox("What value to search and delete in all sheets?", _
                                "Search String", "abcd", Type:=2)
If MyStr = "False" Or MyStr = vbNullString Then Exit Sub

If MsgBox("Should string match to WHOLE cell values only?" & vbLf & _
          "(NO means rows with partial matches will be deleted, too.", _
          vbYesNo, "Whole Cell Match Only?") = vbYes Then
    MatchWhole = 1
Else
    MatchWhole = 2
End If

On Error Resume Next

For Each ws In Worksheets
    Do
        Set strFIND = ws.Cells.Find(MyStr, LookIn:=xlValues, LookAt:=MatchWhole)
        If Not strFIND Is Nothing Then
            strFIND.EntireRow.Delete xlShiftUp
        Else
            Exit Do
        End If
    Loop
Next ws

End Sub




Nothing says "thanks" like a steak dinner!
PayPal - The safer, easier way to pay online!

Comments