Solved: Small macro, simpler way of writing it?

[starsky]

Hi,

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.

Thanks

[vba]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:="="

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

ActiveSheet.AutoFilterMode = False


End Sub[/vba]

[Aflatoon]

You could put it in a loop:

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

[Bob Phillips]

Untested

[vba]


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
[/vba]

[starsky]

Both work and are informative, thank you.

Quickie - is there any disadvantage to using:

[VBA]Set Rng = Range("A1").CurrentRegion[/VBA]

instead of

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

[Bob Phillips]

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.