how to automatically delete non-contiguous rows

  • Thread starter Hucleberry Hound
  • Start date
H

Hucleberry Hound

Is it possible to define a large data field then delete non-contiguous rows,
like every fifth row, without having to delete each row one at a time?
 
R

Ron de Bruin

Hi

One way to insert a column with a formula and use
SpecialCells(xlCellTypeBlanks) to delete the rows

Test it on a copy of your workbook

Sub test1()
Application.ScreenUpdating = False
Dim myRows As Long
Range("A1").EntireColumn.Insert
myRows = ActiveSheet.UsedRange.Rows.Count

With Range(Cells(1, 1), Cells(myRows, 1))
.FormulaR1C1 = "=IF(MOD(ROW(),5)=1,""Keep"","""")"
.Value = .Value
End With
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Range("A1").EntireColumn.Delete
Application.ScreenUpdating = True
End Sub
 
Top