Excel

Delete Rows Based on Column Criteria

Ease of Use

Easy

Version tested with

2003 

Submitted by:

brettdj

Description:

This example searches a user entered column for a user-entered string and then deletes any rows where the string is found. The current example finds and deletes rows only if the cell contents in the user entered column exactly match the user-entered string. 

Discussion:

The code can be easily modified to search for a partial string match rather than the entire cell. The MatchCase property of the Find Object can be brought into play if the deletion should be case sensitive. 

Code:

instructions for use

			

Option Explicit Sub KillRows() Dim MyRange As Range, DelRange As Range, C As Range Dim MatchString As String, SearchColumn As String, ActiveColumn As String Dim FirstAddress As String, NullCheck As String Dim AC 'Extract active column as text AC = Split(ActiveCell.EntireColumn.Address(, False), ":") ActiveColumn = AC(0) SearchColumn = InputBox("Enter Search Column - press Cancel to exit sub", "Row Delete Code", ActiveColumn) On Error Resume Next Set MyRange = Columns(SearchColumn) On Error Goto 0 'If an invalid range is entered then exit If MyRange Is Nothing Then Exit Sub MatchString = InputBox("Enter Search string", "Row Delete Code", ActiveCell.Value) If MatchString = "" Then NullCheck = InputBox("Do you really want to delete rows with empty cells?" & vbNewLine & vbNewLine & _ "Type Yes to do so, else code will exit", "Caution", "No") If NullCheck <> "Yes" Then Exit Sub End If Application.ScreenUpdating = False 'to match the WHOLE text string Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole) 'to match a PARTIAL text string use this line 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlpart) 'to match the case and of a WHOLE text string 'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True) If Not C Is Nothing Then Set DelRange = C FirstAddress = C.Address Do Set C = MyRange.FindNext(C) Set DelRange = Union(DelRange, C) Loop While FirstAddress <> C.Address End If 'If there are valid matches then delete the rows If Not DelRange Is Nothing Then DelRange.EntireRow.Delete Application.ScreenUpdating = True End Sub

How to use:

  1. Copy the above code.
  2. Open any workbook.
  3. Press Alt + F11 to open the Visual Basic Editor (VBE).
  4. From the Menu, choose Insert-Module.
  5. Paste the code into the right-hand code window.
  6. Close the VBE, save the file if desired.
 

Test the code:

  1. Go to Tools-Macro-Macros and double-click KillRows
 

Sample File:

Delete Rows Based on Column Criteria(KB 13).zip 11.89KB 

Approved by mdmackillop


This entry has been viewed 2155 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express