How do I delete rows selectively?

  • Thread starter Thread starter SouthAfricanStan
  • Start date Start date
S

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?
 
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?
Click to expand...


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
 
Try this little sub in the worksheet VB module

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

HTH
Regards,
Howard
 
Try this little sub in the worksheet VB module

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

HTH
Regards,
Howard
 
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
 
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
 
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.
 
Or how about no autofilter at all?

Select column F and F5>Special>Blanks>OK

Edit>Delete>Entire row.


Gord Dibben MS Excel MVP
 
Back
Top