 |
| FREE Excel STUFF |
Search |
| PRODUCTS |
| Development |
| Contact |
Excel Online |
|
Complete Excel Excel Training Course. Instant Buy/Download, 30 Day Money Back Guarantee FREE DOWNLOADS SmartVBA | SmartVB6 | CodeVBA | CodeVB6 | Code Generator Pro | TraderXL Pro Package. MORE.. |
Current Special! Complete Excel
Excel
Training Course
for Excel 97 - Excel 2003, only $145.00.
$59.95 Instant
Buy/Download,
30 Day Money Back Guarantee & Free
Excel Help for LIFE!
Lot's of free Excel VBA . Got any Excel Questions? Free Excel Help
Delete Rows Based on Condition
One very common question is: "how
can I delete rows from my Excel Worksheet based on a specified criteria, or
condition?" Below I have included the fastest 2 ways this can be done
with the use of the AutoFilter being the fastest by far. Both examples are
based on your data being in a contiguous range with the criteria/condition
to be looked for in the relative column of the table you specify. The first
row of your table should be headings.
Before running either code you should
select any single cell in your table.
Sub DeleteRowsFastest()
Dim rTable As Range
Dim lCol As long
Dim vCriteria
On Error Resume Next
'Determine the table range
With Selection
If .Cells.Count > 1 Then
Set rTable = Selection
Else
Set rTable = .CurrentRegion
On Error GoTo 0
End If
End With
'Determine if table range is valid
If rTable Is Nothing Or rTable.Cells.Count = 1 Or WorksheetFunction.CountA(rTable) < 2 Then
MsgBox "Could not determine you table range.", vbCritical, "Ozgrid.com"
Exit Sub
End If
'Get the criteria in the form of text or number.
vCriteria = Application.InputBox(Prompt:="Type in the criteria that macthing rows should be deleted. " _
& "If the criteria is in a cell, point to the cell with your mouse pointer", _
Title:="CONDITIONAL ROW DELETION CRITERIA", Type:=1 + 2)
'Go no further if they Cancel.
If vCriteria = "False" Then Exit Sub
'Get the relative column number where the criteria should be found
lCol = Application.InputBox(Prompt:="Type in the relative number of the column where " _
& "the criteria can be found.", Title:="CONDITIONAL ROW DELETION COLUMN NUMBER", Type:=1)
'Cancelled
If lCol = 0 Then Exit Sub
'Remove any existing AutoFilters
ActiveSheet.AutoFilterMode = False
'Filter table based on vCriteria using the relative column position stored in lCol.
rTable.AutoFilter Field:=lCol, Criteria1:=vCriteria
'Delete all rows that are NOT hidden by AutoFilter.
rTable.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
'Remove AutoFilters
ActiveSheet.AutoFilterMode = False
On Error GoTo 0
End Sub
Sub DeleteRowsSecondFastest()
Dim rTable As Range
Dim rCol As Range, rCell As Range
Dim lCol As Long
Dim xlCalc As XlCalculation
Dim vCriteria
On Error Resume Next
'Determine the table range
With Selection
If .Cells.Count > 1 Then
Set rTable = Selection
Else
Set rTable = .CurrentRegion
On Error GoTo 0
End If
End With
'Determin if table range is valid
If rTable Is Nothing Or rTable.Cells.Count = 1 Or WorksheetFunction.CountA(rTable) < 2 Then
MsgBox "Could not determine you table range.", vbCritical, "Ozgrid.com"
Exit Sub
End If
'Get the criteria in the form of text or number.
vCriteria = Application.InputBox(Prompt:="Type in the criteria that macthing rows should be deleted. " _
& "If the criteria is in a cell, point to the cell with your mouse pointer", _
Title:="CONDITIONAL ROW DELETION CRITERIA", Type:=1 + 2)
'Go no further if they Cancel.
If vCriteria = "False" Then Exit Sub
'Get the relative column number where the criteria should be found
lCol = Application.InputBox(Prompt:="Type in the relative number of the column where " _
& "the criteria can be found.", Title:="CONDITIONAL ROW DELETION COLUMN NUMBER", Type:=1)
'Cancelled
If lCol = 0 Then Exit Sub
'Set rCol to the column where criteria should be found
Set rCol = rTable.Columns(lCol)
'Set rCell to the first data cell in rCol
Set rCell = rCol.Cells(2, 1)
'Store current Calculation then switch to manual.
xlCalc = Application.Calculation
Application.Calculation = xlCalculationManual
'Loop and delete as many times as vCriteria exists in rCol
For lCol = 1 To WorksheetFunction.CountIf(rCol, vCriteria)
Set rCell = rCol.Find(What:=vCriteria, After:=rCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Offset(-1, 0)
rCell.Offset(1, 0).EntireRow.Delete
Next lCol
'Put back calculation to how it was.
Application.Calculation = xlCalc
On Error GoTo 0
End Sub
Excel Dashboard Reports & Excel Dashboard Charts 50% Off
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
