CODEWITHSUNDEEP
vbaexcel
Pedro Lastra
Pedro Lastra

Reputation: 63

vba excel delete data->remain 0

With Range("Q10", Range("Q" & Rows.Count).End(xlUp))
 .Replace "*-", "", xlPart, xlByRows, False, False, False
 .Replace " -", "", xlPart, xlByRows, False, False, False
End With
With Range("X10", Range("X" & Rows.Count).End(xlUp))
 .Replace "*-", "", xlPart, xlByRows, False, False, False
 .Replace " -", "", xlPart, xlByRows, False, False, False
End With

Dim threeCell As Range
For Each threeCell In Range("Q10", Range("Q" & Rows.Count).End(xlUp))
With threeCell
    .Value = Trim(.Value)
End With
Next threeCell

Hello everyone im using that code to delete data from a column. That column contains data like "DE-45" "CZ-DZX" and it works perfect it deletes "DE-" and it leaves the cells just with what i want the problem is when a cell is something like this "DE-03" it gives me a "3" instead a "03".

How can i fix this?

Thanks!

Upvotes: 0

Views: 27

Answers (1)

Verzweifler
Verzweifler

Reputation: 940

This might be a bit hacky, but it works:

Change .Replace "*-", "", xlPart, xlByRows, False, False, False to:

.Replace "*-", "'", xlPart, xlByRows, False, False, False

Note the single ' as parameter for Replacement. This will result in a value like '05, which Excel interprets as String - thus not removing any Zeros.

Upvotes: 2

Related Questions