Text Functions‎ > ‎

Delete Rows

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

Part 1 - Delete Rows that are completely blank

"I have a sheet that has unused rows scattered throughout, I would like a macro that would delete those rows."

  1. Only rows that are devoid of any constants or formulas will be deleted
  2. All the rows flagged for deletion will be deleted all together one time at the end for best efficiency


Option Explicit

Sub DeleteEmptyRows()
'Jerry Beaucaire   2/25/2015
'Deletes all the found rows all at once for best performance speed

Dim LR As Long, Rw As Long, DelRNG As Range

LR = Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
Set DelRNG = Range("A" & LR)

For Rw = 1 To LR
    If WorksheetFunction.CountA(Range("A" & Rw).EntireRow) = 0 Then
        Set DelRNG = Union(DelRNG, Range("A" & Rw))
    End If
Next Rw

DelRNG.EntireRow.Delete xlShiftUp
End Sub

Part 2 - Delete Rows On All Sheets by String Search

"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?"

  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


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
    MatchWhole = 2
End If

On Error Resume Next

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

End Sub

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