Need help with minor adjustment on macro

[skyport]

I am currently using a macro that eliminates every occurrence where a colon, blank space or zero is the very first character in any cell within columns A,C and E. The problem I am having is that I now need to have formulas in those columns to perform other functions as well and when I run the macro it still properly gets rid of the unwanted characters as it should however, it also deletes all the formulas from the subsequent cells all the way down each column. Hopefully someone can help with the suggestion on how to overcome the problem and still have the macro eliminate the unwanted characters while preserving the formulas that exist in the subsequent cells. Below is the code I'm using:


Sub RemoveLeadingSpacesColonsAndZeroes_v2()
Dim r As Long, c As Long, Data As Variant, i As Long, s As String
With Range("A1:G" & Cells(Rows.Count, "A").End(xlUp).Row)
Data = .Value
For r = 1 To UBound(Data, 1)
For c = 1 To UBound(Data, 2)
s = Data(r, c)
i = 1
Do While Mid(s, i, 1) Like "[ :0]"
i = i + 1
Loop
If i > 1 Then Data(r, c) = Mid(s, i)
Next
Next
.Value = Data
End With
End Sub

 

[Fishboy]

I am currently using a macro that eliminates every occurrence where a colon, blank space or zero is the very first character in any cell within columns A,C and E. The problem I am having is that I now need to have formulas in those columns to perform other functions as well and when I run the macro it still properly gets rid of the unwanted characters as it should however, it also deletes all the formulas from the subsequent cells all the way down each column. Hopefully someone can help with the suggestion on how to overcome the problem and still have the macro eliminate the unwanted characters while preserving the formulas that exist in the subsequent cells. Below is the code I'm using:


Sub RemoveLeadingSpacesColonsAndZeroes_v2()
Dim r As Long, c As Long, Data As Variant, i As Long, s As String
With Range("A1:G" & Cells(Rows.Count, "A").End(xlUp).Row)
Data = .Value
For r = 1 To UBound(Data, 1)
For c = 1 To UBound(Data, 2)
s = Data(r, c)
i = 1
Do While Mid(s, i, 1) Like "[ :0]"
i = i + 1
Loop
If i > 1 Then Data(r, c) = Mid(s, i)
Next
Next
.Value = Data
End With
End Sub

Hi skyport,

Try out the following in a COPY of your workbook:

Sub TEST()
Dim Cell As Range, cRange As Range
    LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
        Set cRange = Range("A1:A" & LastRow)
            For Each Cell In cRange
                If Left(Cell.Value, 1) Like "[ :0]" Then
                Cell.ClearContents
                End If
            Next Cell
        Set cRange = Range("C1:C" & LastRow)
            For Each Cell In cRange
                If Left(Cell.Value, 1) Like "[ :0]" Then
                Cell.ClearContents
                End If
            Next Cell
        Set cRange = Range("E1:E" & LastRow)
            For Each Cell In cRange
                If Left(Cell.Value, 1) Like "[ :0]" Then
                Cell.ClearContents
                End If
            Next Cell


End Sub

If it works as intended I'm sure I can make it more streamlined

 

[skyport]

Greetings Fishboy

Thanks for helping me. I ran the code and the following is what I observed:

1. The good news is it did preserve the formulas properly throughout each column.

2. Column A is a column that is primarily used to enter dates and what the code did was completely delete all dates that did not have double digits for the month (first two characters of the date).

Examples:
12-06-1950 was left intact, properly so.
9-25-1950 was completely deleted entirely, which should have been left intact as well.
: 12-13-1950 in this case, the code not only eliminated the unwanted : And two spaces following it, but also eliminated the entire date which should be preserved.

In general what it is doing is perhaps interpreting the single digit month portion of the date as having a zero in front of the nine in the above example and therefore eliminating it entirely from the cell.

3. Even in other columns such as C and E which is mostly text data, when the code finds a comma,space or colon as the first character, rather than just eliminating only the unwanted character (s), it eliminates entirely everything in that cell.

Hopefully the above feedback will help so appropriate adjustments might be tried. I want to thank you again for taking the time to try to help me.

 

[skyport]

Bump

 

[Fishboy]

Even in other columns such as C and E which is mostly text data, when the code finds a comma,space or colon as the first character, rather than just eliminating only the unwanted character (s), it eliminates entirely everything in that cell.

Right, I think I misunderstood or misread the original post. In my code I specifically instruct it to fully clear the cells where it finds matching criteria. Just to clarify that I am now understanding correctly, you only want the leading space, zero or colon removed?

I will go back to the drawing board and get back to you later with some updated code.

 

[skyport]

what you say is correct. There may be more than one of those characters leading off a given cell such as " : 09/24/2015 " as an example. The idea would be to eliminate them all and leave in this case just the 9/24/20015

 

[Fishboy]

what you say is correct. There may be more than one of those characters leading off a given cell such as " : 09/24/2015 " as an example. The idea would be to eliminate them all and leave in this case just the 9/24/20015

Updated code below:

Sub NEWTEST()
Dim Cell As Range, cRange As Range
    LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
        Set cRange = Range("A1:A" & LastRow)
            For Each Cell In cRange
                If Left(Cell.Value, 1) Like "[ :0]" Then
                Cell.Value = Right(Cell.Value, Len(Cell.Value) - 1)
                Cell.Value = Trim(Cell.Value)
                End If
            Next Cell
        Set cRange = Range("C1:C" & LastRow)
            For Each Cell In cRange
                If Left(Cell.Value, 1) Like "[ :0]" Then
                Cell.Value = Right(Cell.Value, Len(Cell.Value) - 1)
                Cell.Value = Trim(Cell.Value)
                End If
            Next Cell
        Set cRange = Range("E1:E" & LastRow)
            For Each Cell In cRange
                If Left(Cell.Value, 1) Like "[ :0]" Then
                Cell.Value = Right(Cell.Value, Len(Cell.Value) - 1)
                Cell.Value = Trim(Cell.Value)
                End If
            Next Cell
End Sub

 

[skyport]

I think that did the trick and solved the problem. Seems to work real good. I want to thank you for sticking with it and helping me. people like yourself really make this site and the world a better place with your shared ideas and solutions.

 

[Fishboy]

I think that did the trick and solved the problem. Seems to work real good. I want to thank you for sticking with it and helping me. people like yourself really make this site and the world a better place with your shared ideas and solutions.

You're most welcome. Thanks for the positive feedback