Deleting redundant records

[Hayley]

Hi. I have a list of 10,000 plus rows with several
headings over columns A to L. I want to delete any row
that contains an incomplete record, which means here any
record in which column F is blank. Is it possible to do
this without having to go through the list deleting rows
manually, and is it possible to do it in a way that
deletes the whole row (moving the list up) rather than
just the data (so leaving gaps in the list). Many thanks
if you can help.

Hayley

 

[Frank Kabel]

Hi
one way:
- apply a filter (Data - Filter Autofilter')
- filter for blank rows
- delete these rows
- remove the filter

 

[Chip Pearson]

Hayley,

Try some code like the following:

Sub DeleteRows()
Dim LastRow As Long
Dim RowNdx As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For RowNdx = LastRow To 2 Step -1
If Cells(RowNdx, "F").Value = "" Then
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

Thanks. Not well enough up on code to give it a go but
Frank's suggestion worked so I'm happy. Very grateful to
you both.

Hayley