Delete Blank Rows in a pattern

[GregR]

I have a spreadsheet that beginning in Row 6 has data every thrid row
for about 7500 rows. I want to delete all blank rows to the last
filled row. The pattern is such;
Blank
Blank
Data
Blank
Blank
Data
Blank
Blank
Data.............etc
Is there a fast way in code to segregate the blank rows and delete
them all? TIA

Greg

 

[Gary Keramidas]

use autofilter the filter the blank rows then delete them

 

[Bob Phillips]

Dim rng As Range
Set rng = Columns(1).SpecialCells(xlCellTypeBlanks)
rng.EntireRow.Delete


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Chip Pearson]

Greg,

The following code will delete the rows whose value in column A is blank.
Note that the code works from the bottom of the list moving upwards. This
is done to prevent the RowNdx variable from pointing to the wrong row after
a Delete operation. As a generaul rule, if you have loops that either Insert
or Delete a row, you should always work from the bottom of the list and move
upwards to the top of the list.


Sub AAA()
Dim LastRow As Long
Dim StartRow As Long
Dim RowNdx As Long

StartRow = 1 '<<< CHANGE AS DESIRED
With ThisWorkbook.Worksheets("Sheet1") '<<< CHANGE SHEET NAME AS DESIRED
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For RowNdx = LastRow To StartRow Step -1
If .Cells(RowNdx, "A").Value = vbNullString Then
.Rows(RowNdx).Delete
End If
Next RowNdx
End With
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)