How do I delete rows selectively?

[SouthAfricanStan]

Win XP, Office 2003
I have a worksheet containing +- 900 rows.
In col F, certain cells have text values, others are blank.
How do I delete ONLY the rows in which the cells in col F are blank?

 

[craiglittleperth]

Win XP, Office 2003
I have a worksheet containing +- 900 rows.
In col F, certain cells have text values, others are blank.
How do I delete ONLY the rows in which the cells in col F are blank?

Hi,

just spent hours on this myself.

The below works for me.

\Craig
Sub TWODelete_with_Autofilter_removing_NA()

Dim DeleteValue As String
Dim rng As Range


Sheets("Invoice Summary - Split").Select
Range("A1").Select

DeleteValue = "#N/A"
' This will delete the rows with "#N/A" in the Range("B:B")
With ActiveSheet
.Range("B:B").AutoFilter Field:=1, Criteria1:=DeleteValue
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete

End With
.AutoFilterMode = False
End With

Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select

End Sub

 

[L. Howard Kittle]

Try this little sub in the worksheet VB module

Sub RowBeGone()
Columns("F").SpecialCells(xlCellTypeBlanks).Delete
End Sub

HTH
Regards,
Howard

 

[L. Howard Kittle]

Try this little sub in the worksheet VB module

Sub RowBeGone()
Columns("F").SpecialCells(xlCellTypeBlanks).Delete
End Sub

HTH
Regards,
Howard

 

[MartinW]

Hi Stan,

For a non VB solution you could try this.
Insert a helper column G.
Put =ISBLANK(F1) in cell G1
Grab the fill handle and drag down to the end of your data.
Go to Data>Filter>Autofilter
Then from the pulldown at the top of column G select TRUE
Now only the rows with a blank in F will be showing.
Select the entire rows by highlighting the row headers.
Right click and select delete.
Turn Autofilter off by going back to Data>Filter>Autofilter.
And you are done.

HTH
Martin

 

[Don Guillett]

or
Sub deletenarows()
lr = Cells(Rows.Count, "b").End(xlUp).Row
With Range("b2:b" & lr)
.AutoFilter Field:=1, Criteria1:="#N/A"
.EntireRow.Delete
End With
End Sub

 

[KC Rippstein]

Didn't think a helper column would be necessary. You can just auto filter
column F for "blanks", hit F5 and select "visible cells", then delete empty
rows and remove the auto filter.

 

[Gord Dibben]

Or how about no autofilter at all?

Select column F and F5>Special>Blanks>OK

Edit>Delete>Entire row.


Gord Dibben MS Excel MVP

 

[MartinW]

Yep! At least my tunnel-vision is in good working order<bg>

Cheers
Martin