Deleting Duplicate Rows In a Selection with a True Statement

[Josh heep]

Hey,

I was wondering if there is a more efficient way to write this macro.
It searches through a selected range and deletes any row that is
valued as True. I made it to remove duplicates based on various
criteria that I change from project to project. Thanks for any
insight!

Sub Delete_True_Rows_In_Selection()

Dim TotalRows As Integer
Dim FirstRow As Integer
Dim Col As Integer
Dim Row As Integer

TotalRows = Selection.Rows.Count
FirstRow = Selection.Row()
Col = Selection.Column()

Application.ScreenUpdating = False

On Error Resume Next
For Row = FirstRow To TotalRows
If Cells(Row, Col).Value = True Then
Rows(Row).Delete
Row = Row - 1
End If
Next Row

Application.ScreenUpdating = True

End Sub

 

[Bernie Deitrick]

Josh,

The fastest way is to sort, autofilter based on the deletion criteria
column, select visible cells, and then delete rows. No looping involved.

This macro assumes that there is a header row, no blank columns or rows,
and that the cells with True/False are selected prior to running (but the
header
row cell is NOT selected).

HTH,
Bernie
MS Excel MVP


Sub Delete_True_Rows_In_Selection2()

Dim myR As Range
Dim myS As Range

Set myS = Selection
Set myR = ActiveCell.CurrentRegion
myR.Sort Key1:=myS.Cells(1), Order1:=xlAscending, Header:=xlYes
myR.AutoFilter Field:=myS.Column - myR(1).Column + 1, Criteria1:="TRUE"
myS.SpecialCells(xlCellTypeVisible).EntireRow.Delete
myS.AutoFilter
End Sub

 

[Josh heep]

Thanks for the help Bernie worked great after I removed some merged
cells I had.