Delete Spaces

[CristianDavid]

Hello all; I want to start saying that I like this forum...I started using it a couple of weeks back...I'm fairly new with VBA (in other words a noob) I just started reading a programing book and I get help from a couple of peers that are kinda pro however I'm trying to start solving issues by myself so...here we go (sorry grammar; English is not my first language):

I want to create a macro that delete all the double spaces in a column then each space most be replace by a "/" symbol so far this is what i got

1-On a test workbook I created a macro that remove all the spaces but it only works when I put info one cell at the time

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 1 Then
 Target.Offset(0, 0).Value = WorksheetFunction.Trim(Target)
     End If
End Sub

2-I have another macro that replace all the single spaces to "/" (this is the workbook I'm using the macro doesn't end there but i think this is the important part)

Private Sub Worksheet_Change(ByVal Target As Range)
  
  If Target.Row = 1 Then Exit Sub

  On Error GoTo ErrHandler
  
  Application.EnableEvents = False
  
  If Target.Column = 5 Then
  
      Target.Select
    
    Selection.Find(What:=" ", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
        
    Selection.Replace What:=" ", Replacement:="/", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
  End If

ErrHandler:
  
  Application.EnableEvents = True

End Sub

I want to combine those two and I would like to know what am I doing wrong with the first one; like a brief explanation if possible

thanks in advance

[tom1977]

Hi for start:
first macro works with column 1, second with column 5, is it ok?

this works with column 1

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 1 Then
Target = WorksheetFunction.Trim(Target)
Target = Replace(Target, " ", "/")
End If
Application.EnableEvents = True
End Sub

[CristianDavid]

Wow i thought it was going to take longer to get a reply...awesome

Ok I made a test workbook to try the Trim function but is intended to work on the 5th column (2nd macro)

[Andrew-R]

You're using the worksheet_change event, so this macro will run every time a cell is updated on your worksheet, which doesn't seem to match your requirement to perform this function on a whole column at once. Also, each action (deleting double spaces and replacing single spaces with /s) is set to work only on the cell which has just been updated.

If that's what you're trying to do then I'd go with something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Target.Column = 1 And Target.Cells.Count = 1 Then
  
  While InStr(Target.Value, "  ") > 0
    Target.Value = Replace(Target.Value, "  ", " ")
  Wend
  
  Target.Value = Replace(Target.Value, " ", "/")
  
End If

Application.EnableEvents = True

End Sub

[CristianDavid]

thank you guys for the suggestions Ill try them both; I'm a bit slow tho so Ill get back to you as soon as possible.

here is the full code (just to avoid confusions on what im trying to do)

Private Sub Worksheet_Change(ByVal Target As Range)
  
  If Target.Row = 1 Then Exit Sub

  On Error GoTo ErrHandler
  
  Application.EnableEvents = False
  
  If Target.Column = 5 Then
  
   Target.Select
   
  Target.Value = WorksheetFunction.Trim(Target)
       
        Selection.Find(What:=" ", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
        
    Selection.Replace What:=" ", Replacement:="/", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
  End If
  
  If Target.Column = 13 Or Target.Column = 15 Or Target.Column = 17 Then
     If Target.Value = "Approved" Or Target.Value = "Declined" Then
       
       'ActiveSheet.Unprotect Password:="test"
       
       Target.Offset(0, 1).Value = "=NOW()"
       
       Target.Offset(0, 1).Activate
       
       Selection.Copy
       
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
    
        Target.Offset(1, 0).Activate
    
     Else
        Target.Offset(0, 1).Clear
     End If
     'ActiveSheet.Protect Password:="test"
     
  End If
  
  If Target.Column = 8 Then
     If Target.Value = "A" Or Target.Value = "M" Or Target.Value = "V" Then
       
       Target.Offset(0, 1).Value = "=NOW()"
       
       Target.Offset(0, 1).Activate
       
       Selection.Copy
       
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
    
        Target.Offset(1, 0).Activate
    
    
     Else
        Target.Offset(0, 1).Clear
     End If
     
  End If
  
ErrHandler:
  
  Application.EnableEvents = True


End Sub

I was working on it and made a slight change; putting both codes together here is the part Im working on right now

If Target.Column = 5 Then
  
   Target.Select
   
  Target.Value = WorksheetFunction.Trim(Target)
       
        Selection.Find(What:=" ", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
        
    Selection.Replace What:=" ", Replacement:="/", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
  End If

it seems like is working now however when I pull the data and paste it I get this green pointer "the cell contains a date string represented with only 2 digits for the year." when I click on "convert XX to 20XX" the macro runs perfectly...I tried to record it but it doesn't shown anything; i can use it like it is but just because I'm learning I would like to know if this can be corrected.

thanks again! Ill try your codes

[CristianDavid]

Hi for start:
first macro works with column 1, second with column 5, is it ok?

this works with column 1

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 1 Then
Target = WorksheetFunction.Trim(Target)
Target = Replace(Target, " ", "/")
End If
Application.EnableEvents = True
End Sub

Hi, I tried you code and it works! but i keep getting the same issue "the cell contains a date string represented with only 2 digits for the year." when I click on "convert XX to 20XX" is there a way to chane the format with the macro instead of clicking and changing it?

You're using the worksheet_change event, so this macro will run every time a cell is updated on your worksheet, which doesn't seem to match your requirement to perform this function on a whole column at once. Also, each action (deleting double spaces and replacing single spaces with /s) is set to work only on the cell which has just been updated.

If that's what you're trying to do then I'd go with something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Target.Column = 1 And Target.Cells.Count = 1 Then
  
  While InStr(Target.Value, "  ") > 0
    Target.Value = Replace(Target.Value, "  ", " ")
  Wend
  
  Target.Value = Replace(Target.Value, " ", "/")
  
End If

Application.EnableEvents = True

End Sub

Hi Andre, thanks for your reply, I tried your code but sometimes the first number of the data have a double space, it returns the value as "/2/12/2012", I think I got it cover with Tom's code I just need to figure out how to change the format automatically.

Any suggestions?

Regards