Cannot select not adjacent cells as range

[Livin404]

I'm using a VBA code that works great in removed blank spaces until I try to select cells that are scattered about on my worksheet (select by pressing Ctrl and left click mouse). I am wondering is there something I can do to make the VBA work no matter what cells I select? Thank you,

Sub Trim()
Dim rng As Range
Set rng = Selection
rng.Value = Application.Trim(rng)
End Sub

 

[Michael M]

Try using

Sub Trim()
Dim cell As Range
For Each cell In Selection
  cell = Application.Trim(cell)
Next cell
End Sub

 

[Livin404]

Try using

Sub Trim()
Dim cell As Range
For Each cell In Selection
  cell = Application.Trim(cell)
Next cell
End Sub

Fantastic! works great Cheers!

 

[Michael M]

Glad to help...

 

[Livin404]

Hello, you helped me out the other night, and the VBA you suggested works great. However, I have another macro that will not work if I have the one we worked on on listed in the VBA. I was wondering is there a way I can reword it, so it won't jam up this other macro. The macro I'm referring to swaps the first and last name in a cell. I definitely will use ours more often, but occasionally I will need to use the other. The VBA code for the other macro is: The part it gets hung up on is

mycell.value=Trim

. Thank you so much!

Sub ReverseName_v2()
  Dim myRange As Range, myCell As Range
  Dim NameList As Variant
  
  Set myRange = Application.InputBox("Select one Range that you want to reverse name", "ReverseName", Selection.Address, Type:=8)
  For Each myCell In myRange
    If InStr(myCell.value, " ") > 0 Then
      If MsgBox("switch first 2 words?" & vbCr & myCell.value, vbYesNo, "") = vbYes Then
        NameList = Split(myCell.value & " ", , 3)
        myCell.value = [B]Trim(Jo[/B]in(Array(NameList(1), NameList(0), NameList(2))))
      End If
    End If
  Next
End Sub

Public Function Trunc(ByVal value As Double) As Integer
  ' Truncate by calling Int on the Absolute value then multiply by the sign of the value.
  ' Int cannot truncate doubles that are negative
  Trunc = (Abs(value) / value) * Int(Abs(value))
End Function

 

[Livin404]

Hey, before you commit to any time, I got the issue resolved. I just had to change the Sub title very slightly. Thank you.