Simple deletion macro

  • Thread starter Thread starter Yarroll
  • Start date Start date
Y

Yarroll

Hi,

Is there any simpler (than below) method of deleting a bunch of rows based
on some criteria? (in this case, empty cells). I run the macro below, but it
takes forever to execute. Thanks,

Best regards
Yarroll

Dim i As Long, ile As Long
ile = WorksheetFunction.CountA(ActiveSheet.Range("B:B"))
For i = ile To 1 Step -1
If Cells(i, 1).Value = "" Then
Rows(i).Delete
End If
Next
End Sub
 
Yarroll,

Use Auto-filter on column B, and select a criteria of non-blanks. Delete all
visible rows, with a blue row number to the left.
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Sorry, meant blanks not non-blanks.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Yarroll,

Here's a VBA method of this as well

Sub DeleteRows()
With ActiveSheet
.Range("B1").EntireRow.Insert
.Range("B1").FormulaR1C1 = "Test"
.Columns("B:B").AutoFilter Field:=1, Criteria1:="="
.Cells.SpecialCells(xlCellTypeVisible).EntireRow.Delete
.Range("B1").EntireRow.Delete
End With
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top