if cell start with 0 clean the cell from A to Z

[parscon]

I need a VBA code that check column A to Z and if any cell start with 0 clean it

for example if A1 is : 022390340 it will be delete but if A2: 2025540 it will not be delete .

Thank you

[GTO]

Assuming the cells are formatted as Text... maybe something like:



Sub RipValsWLeadingZero()
Dim ary() As Variant
Dim n As Long

ary = Range("A1:A1000").Value

For n = 1 To UBound(ary)
If Len(ary(n, 1)) > 0 Then
If CStr(Left$(ary(n, 1), 1)) = "0" Then
ary(n, 1) = vbNullString
End If
End If
Next

Range("A1:A1000").Value = ary

End Sub


Hope that helps,

Mark

[parscon]

Thank you but this will just work for A column , I need to check column A to Column Z.

Thank you again.

[Aussiebear]

try changing the range values

[parscon]

Yes , I must change by each column , is it possible to check all the column that mean A to Z ?

[ashleyuk1984]

This will do the trick for you.

This will search ALL of the ActiveSheet


Sub DeleteCellsStartingWith0()

For Each Cell In ActiveSheet.UsedRange.Cells
If Left(Cell, 1) = "0" Then Cell.ClearContents
Next

End Sub

This will search JUST columns A to Z


Sub DeleteCellsStartingWith0()

For Each cell In Columns("A:Z").Cells.SpecialCells(xlCellTypeConstants)
If Left(cell, 1) = "0" Then cell.ClearContents
Next

End Sub

[Aussiebear]

Assuming the cells are formatted as Text... maybe something like:



Sub RipValsWLeadingZero()
Dim ary() As Variant
Dim n As Long

ary = Range("A1:Z1000").Value

For n = 1 To UBound(ary)
If Len(ary(n, 1)) > 0 Then
If CStr(Left$(ary(n, 1), 1)) = "0" Then
ary(n, 1) = vbNullString
End If
End If
Next

Range("A1:Z1000").Value = ary

End Sub


Did you try this? As I suggested the range in the initial code by GTO was A1:A1000, is limited. Ashley's code goes beyond your suggested range, but the intent is good. Maybe you need to make use of the Lastrow option

Hope that helps,

Mark