Deleting Duplicate Rows In a Selection with a True Statement
 

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

Deleting Duplicate Rows In a Selection with a True Statement
 

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.Dele te
myS.AutoFilter
End Sub


"Josh heep" wrote in message
...
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

Deleting Duplicate Rows In a Selection with a True Statement
 

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

On Feb 12, 6:20 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
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.Dele te
myS.AutoFilter
End Sub

"Josh heep" wrote in message

...

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