Fix embedded space using VBA

[bkelly]

A data set provided me is space separated but a few cells have an intentional space in them, violating the space separation rule.

In this case, the value "00C" always found in column 2, sometimes has an embedded space.
The cell contents should have the format "00C 123456" but the import broke the contents across two cells.
When "00C" is found as the complete contents in column 2, it must be combined with a space followed by the contents of column 3, then everything from 4 to the end is moved left one column.
I cannot find the right syntax to use in the For Each statement, and in the code to select the range, within one row, from column 4 to the end and paste that range back into the row beginning with column 3.

I always try to use row, column format rather than A2 or AB3 format so please try to go that way.

Thank you for your time.

Sub fix_00C_space()
   Dim found As bool

   Dim current_row As Long  ' replace this with the current row number from the For Each statement, I don't know what that is.
 
   For Each row in this_sheet

      If (.cell(current_row, 2) = "00C") Then
        .cell(current_row, 2) = "00C " & .cell(current_row, 3)
        move_columns_4_through_end_to_the_left_by_one_column()
      End If
   Next
End Sub

 

[bobsan42]

Something like this maybe:

Sub fix_00C_space()
    Dim cc As Range
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    For Each cc In Intersect(ActiveSheet.Columns(2), ActiveSheet.UsedRange)
        With cc
            If cc.Value = "00C" Then
                .Value = "00C " & .Offset(0, 1).Value
                .Offset(0, 1).Delete xlToLeft
            End If
        End With
    Next
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationManual
    End With
End Sub

May not be the fastest runner but should get it done.

 

[bkelly]

Worked great. Found some additional similar errors. That Offset / Delete is new to me. Saved much time.
Thank you for your time and patience.

 

[bobsan42]

May I correct my code: i have left the calculations in manual mode instead of reverting them to automatic.

 

[bobsan42]

Glad it worked. The method is handy, just maybe a bit slow in performance.

 

[bkelly]

Changed the middle lines to this to be a bit more flexible.

        With cc
            If cc.Value = "00C" Or cc.Value = "10G" Then
                .Value = cc.Value & " " & .Offset(0, 1).Value
                .Offset(0, 1).Delete xlToLeft
            End If
        End With

Thanks again.

Edit, and I did not see that shift to xlCalculationAutomatic. Fixed and thanks for noting it. Runs plenty fast.
Now I see more errors with embedded spaces and will use the same technique for fixing those.
Thanks again.

Oh, Please explain a bit about:

    For Each cc In Intersect(ActiveSheet.Columns(2), ActiveSheet.UsedRange)

Mostly that Intersect part.

 

[bkelly]

The edit time limit expired. Now I see some places where "Mr" is followed by one or two blank cells. They must be purged. Here is my attempt.
What change is needed to cc.Value to direct it to the cell in the next column?

With cc
If cc.Value = "Mr" And cc.Value_next_column = "" Then
' .Value = cc.Value & " " & .Offset(0, 1).Value
.Offset(0, 1).Delete xlToLeft
End If
End With

 

[bkelly]

I found errors in a version of the code you gave me and am running it for the fifth time or so, each one different.
It ran quickly on test data of about 400 rows, but takes quite a while on 300 K plus rows.
I am not good with VBA, and work at home. The error messages leave much to be desired. No one to ask but the forums.
Thank you for the code you gave me.

 

[bobsan42]

Oh, Please explain a bit about:

For Each cc In Intersect(ActiveSheet.Columns(2), ActiveSheet.UsedRange)

It checks the cells in column 2 or B, but only the part of it where you have data on the sheet (usedrange).

 

[bobsan42]

The edit time limit expired. Now I see some places where "Mr" is followed by one or two blank cells. They must be purged. Here is my attempt.
What change is needed to cc.Value to direct it to the cell in the next column?

With cc
If cc.Value = "Mr" And cc.Value_next_column = "" Then
' .Value = cc.Value & " " & .Offset(0, 1).Value
.Offset(0, 1).Delete xlToLeft
End If
End With

With cc
If cc.Value = "Mr" And cc.Offset(0, 1).Value = "" Then
.Offset(0, 1).Delete xlToLeft
End If
End With

But you have to run it twice.