View Full Version : Solved: Small macro, simpler way of writing it?

10-20-2009, 04:45 AM

Is there a more elegant way of writing this? It does the job (filtering for blanks in cols L to R, then deleting those rows), but I sense a learning opportunity is being missed.


Sub DelBlanks()
Selection.AutoFilter Field:=12, Criteria1:="="
Selection.AutoFilter Field:=13, Criteria1:="="
Selection.AutoFilter Field:=14, Criteria1:="="
Selection.AutoFilter Field:=15, Criteria1:="="
Selection.AutoFilter Field:=16, Criteria1:="="
Selection.AutoFilter Field:=17, Criteria1:="="
Selection.AutoFilter Field:=18, Criteria1:="="

Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete

ActiveSheet.AutoFilterMode = False

End Sub

10-20-2009, 05:34 AM
You could put it in a loop:

Dim n as long
for n = 12 to 18
Selection.AutoFilter Field:=n, Criteria1:="="
next n

10-20-2009, 05:37 AM

With Selection

.AutoFilter Field:=12, Criteria1:="="
.AutoFilter Field:=13, Criteria1:="="
.AutoFilter Field:=14, Criteria1:="="
.AutoFilter Field:=15, Criteria1:="="
.AutoFilter Field:=16, Criteria1:="="
.AutoFilter Field:=17, Criteria1:="="
.AutoFilter Field:=18, Criteria1:="="
End With

Set Rng = Range("A1").Resize(Cells(Rows.Count, "A").End(xlUp).Row)
If Not Rng Is Nothing Then

Rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete

ActiveSheet.AutoFilterMode = False

10-20-2009, 06:12 AM
Both work and are informative, thank you.

Quickie - is there any disadvantage to using:

Set Rng = Range("A1").CurrentRegion

instead of

Set Rng = Range("A1").Resize(Cells(Rows.Count, "A").End(xlUp).Row)

10-20-2009, 06:44 AM
No disadvantage as long as you don't have discontiguous blocks of data, in which case you can use UsedRange.

The former will also use all columns, not just A.