Deleting Rows

[amboo_radley]

I have a data set that looks like this:

LA BEL: M_FRONT _HEIGHT
Po ints Taken: 1
Actual Nominal #NAME? #NAME? Dev Out/Tol
Y: 1808.608 1808.8 1.5 -1.5 -0.192 **********


LA BEL: M_REAR_ HEIGHT
Po ints Taken: 1
Actual Nominal #NAME? #NAME? Dev Out/Tol
Y: 1808.3624 1808.8 1.5 -1.5 -0.4376 **********


I want to delete all the rows except those that have Y: in the first column.
How would I do that? Everything I have tried so far has only deleted some
of the rows, but not all.

 

[Michael]

Set up an auto filter.
Select the whole Column where the Y appears, then from the main menu
Click on Data -> Filter -> Auto Filter
You will see a drop dow box appear in your column; click on it and find the
Y,this should select all of the rows that contain a Y. Then click on the rows
and hit delete, it will delete only the visible rows. The follow the same
procedure to remove the filter as you did to set it up.

 

[Michael]

Oops,
You should select Custom from the drop down the select Does not begin with Y
so you will leave all of the other rows you want to delete.
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.

 

[amboo_radley]

This does not work on the blank rows.

Oops,
You should select Custom from the drop down the select Does not begin with Y
so you will leave all of the other rows you want to delete.
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.

 

[ryguy7272]

Give this a try:
Sub Delete_with_Autofilter()
Dim DeleteValue As String
Dim rng As Range

With ActiveSheet
.Range("A1:A100").AutoFilter Field:=1, Criteria1:="<>Y*"
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
End Sub

Regards,
Ryan---