Statement in Macro. If target cell contents start with a "z", then select target range and delete.

[colmodoc]

Hi.
Looking for help on a macro please.
Example: Column A contains data. I need an statement which can delete the cell contents of a certain range say A2:A12 only if Cell A2 contents begins with the letter "z"
Thanks for viewing.

 

[BarryL]

will it always be a lowercase "z"?

Sub del()
If Left(Range("A2").Value, 1) = "z" Then Range("A2:A12").Delete
End Sub

 

[BarryL]

will it always be a lowercase "z"?

Sub del()
If Left(Range("A2").Value, 1) = "z" Then Range("A2:A12").Delete
End Sub

if not then

Sub del()
If Left(Ucase(Range("A2").Value), 1) = "Z" Then Range("A2:A12").Delete
End Sub

 

[colmodoc]

Thank you Barry for your guidance on this.
Z will not always be lower case so thank you for the update above.
Additionally the starting point for occurrences of Z may not always be A2: Can the statement be altered to 'find the first occurrence of Z in ColumnA' and then to select from that point down to a defined cell (A135)?

 

[BarryL]

try

Sub del()
Dim rng As Range
For Each rng In Range("A2:A135")
If Left(UCase(rng.Value), 1) = "Z" Then Range(Cells(rng.Row, 1), Cells(135, 1)).Delete
Next
End Sub

 

[Peter_SSs]

.. or more directly & less repetitively

Sub ClearFromZ()
  Dim FirstZ As Range
  
  Set FirstZ = Range("A2:A135").Find(What:="z*", After:=Range("A135"), LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious, MatchCase:=False)
  If Not FirstZ Is Nothing Then Range(FirstZ, Range("A135")).ClearContents
End Sub

 

[colmodoc]

That's fantastic Barry and Peter.
And it does very much help.
Thank you both.
Colm

 

[Peter_SSs]

You are very welcome. Thanks for letting us know.