need Macro to delete entire rows that contain letter Z in Col A

skyport

Active Member
Joined
Aug 3, 2014
Messages
374
need Macro to delete entire rows that contain letter Z in Col A

Can someone please help
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
I know there's ways to do this without loops, but here:
Code:
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
 
Upvote 0
Assuming you mean Z is the only thing in the cell and you have a header row then try...

Code:
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
 
Last edited:
Upvote 0
I want to thank you both for helping and here are the results:

NickRed18 it clears the content but does not delete the row which was needed.

Mark858 seems to work very well except although I do not have the first row set to be used as a header, the macro does not eliminate the first row even if there is a Z in A1. Other than that issue, it works great
 
Upvote 0
skyport,

Here is another macro solution for you to consider that does not use filtering, or, looping thru the rows in column A, and, should be very fast.

Sample raw data:


Excel 2007
A
1Title Z
2A
3B
4Z
5Zoo
6C
7Z
8D
9Zone
10F
11
Sheet1


And, after the macro:


Excel 2007
A
1A
2B
3C
4D
5F
6
7
8
9
10
11
Sheet1



Code:
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
 
Upvote 0
the macro does not eliminate the first row even if there is a Z in A1. Other than that issue, it works great

Autofilter only works correctly with a header row which is why I specified it, if you don't have one then insert a blank row at the start of the code and then delete it at the end (or use the code Hiker95 posted although his is currently a little different to mine as it deletes the row where the cell in column A contains a Z anywhere in the cell and not if it is only the Z in the cell but if you want to change it you just need to remove the 2 asterisks).
 
Upvote 0
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.

skyport,

Thanks for the feedback.

You are very welcome. Glad I could help.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top