Sub DeleteZ()
Dim i, j As Integer
For i = 1 To 16 '1 to however long your column is
For j = 1 To Len(Cells(i, 1))
If InStr(Cells(i, 1), "z") <> 0 Then
Rows(i).ClearContents
End If
Next j
Next i
End Sub
Sub REMOVEZ()
With Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
.AutoFilter Field:=1, Criteria1:="Z"
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).EntireRow.Delete Shift:=xlUp
.AutoFilter
End With
End Sub
Excel 2007 | |||
---|---|---|---|
A | |||
1 | Title Z | ||
2 | A | ||
3 | B | ||
4 | Z | ||
5 | Zoo | ||
6 | C | ||
7 | Z | ||
8 | D | ||
9 | Zone | ||
10 | F | ||
11 | |||
Sheet1 |
Excel 2007 | |||
---|---|---|---|
A | |||
1 | A | ||
2 | B | ||
3 | C | ||
4 | D | ||
5 | F | ||
6 | |||
7 | |||
8 | |||
9 | |||
10 | |||
11 | |||
Sheet1 |
Sub skyport()
' hiker95, 06/28/2017, ME1011874
Dim Addr As String
Addr = Range("A1", Cells(Rows.Count, "A").End(xlUp)).Address
Range(Addr) = Evaluate(Replace("IF(ISNUMBER(SEARCH(""*Z*"",@)),""#N/A"",@)", "@", Addr))
Range(Addr).SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End Sub
the macro does not eliminate the first row even if there is a Z in A1. Other than that issue, it works great
Re: need Macro to delete entire rows that contain letter Z in Col A
Hiker Hello once again. You did it again. Works like a charm.
I was wondering if you could be kind enough to see the post at:
https://www.mrexcel.com/forum/excel-...ml#post4856779