VBA - Defining a Range within a Trim

G

gazwilliamson

New Member
Joined
Jun 4, 2019
Messages
9
hi

first post - looking for help creating a Trim VBA for a specific range on a worksheet.

Range in question is "S2:S200000", on a worksheet called "RAW DATA". Aim of this is to remove extra spacing before and after the text in the cell

Have done some digging online and have found some but struggling to define a range in a sheet - vba skills are very basic

Thanks

:)
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
Hi & welcome to MrExcel.
How about
Code: 
Sub gazwilliamson()
   With Sheets("Raw Data")
      With .Range("S2", .Range("S" & Rows.Count).End(xlUp))
         .Value = Evaluate("if({1},trim(" & .Address & "))")
      End With
   End With
End Sub
 
Upvote 0
Code: 
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]   Dim aCell AsRange[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]    Dim wsh AsWorksheet[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Times New Roman][COLOR=#000000] [/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Times New Roman][COLOR=#000000] [/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]    Application.StatusBar = "ProcessingWorksheets... Please do not disturb..."[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]    DoEvents[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Times New Roman][COLOR=#000000] [/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]   Application.ScreenUpdating = False[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Times New Roman][COLOR=#000000] [/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]    For Each wshIn Worksheets[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]        With wsh[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]           Application.StatusBar = "Processing Worksheet " & _[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]                                    .Name &". Please do not disturb..."[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]           DoEvents[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Times New Roman][COLOR=#000000] [/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]            ForEach aCell In .UsedRange[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]               If Not aCell.Value = "" And aCell.HasFormula = False Then[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]                   With aCell[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]                        .Value = Replace(.Value, Chr(160),"")[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]                       .Value = Application.WorksheetFunction.Clean(.Value)[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]                       .Value = Trim(.Value)[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]                   End With[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]               End If[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]            NextaCell[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]        End With[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]    Next wsh[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Times New Roman][COLOR=#000000] [/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]   Application.ScreenUpdating = True[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][COLOR=#000000][FONT=Times New Roman]Application.StatusBar = "Done"[/FONT][/COLOR]
[/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]
 
Upvote 0
Fluff said:
Hi & welcome to MrExcel.
How about
Code: 
Sub gazwilliamson()
   With Sheets("Raw Data")
      With .Range("S2", .Range("S" & Rows.Count).End(xlUp))
         .Value = Evaluate("if({1},trim(" & .Address & "))")
      End With
   End With
End Sub
Click to expand...

Hi thank for picking this up!

Unfortunately, there are still spaces (most likely returns) before the data. The one which worked, but I think went through everything was this (can't remember which website, so apologise for not crediting)

Code: 
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]   Dim aCell AsRange[/FONT][/COLOR]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]    Dim wsh AsWorksheet[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Times New Roman][COLOR=#000000] [/COLOR][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Times New Roman][COLOR=#000000] [/COLOR][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]    Application.StatusBar = "ProcessingWorksheets... Please do not disturb..."[/FONT][/COLOR]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]    DoEvents[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Times New Roman][COLOR=#000000] [/COLOR][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]   Application.ScreenUpdating = False[/FONT][/COLOR]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Times New Roman][COLOR=#000000] [/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]    For Each wshIn Worksheets[/FONT][/COLOR]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]        With wsh[/FONT][/COLOR]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]           Application.StatusBar = "Processing Worksheet " & _[/FONT][/COLOR]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]                                    .Name &". Please do not disturb..."[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]           DoEvents[/FONT][/COLOR]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Times New Roman][COLOR=#000000] [/COLOR][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]            ForEach aCell In .UsedRange[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]               If Not aCell.Value = "" And aCell.HasFormula = False Then[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]                   With aCell[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]                        .Value = Replace(.Value, Chr(160),"")[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]                       .Value = Application.WorksheetFunction.Clean(.Value)[/FONT][/COLOR]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]                       .Value = Trim(.Value)[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]                   End With[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]               End If[/FONT][/COLOR]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]            NextaCell[/FONT][/COLOR]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]        End With[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]    Next wsh[/FONT][/COLOR]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Times New Roman][COLOR=#000000] [/COLOR][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]   Application.ScreenUpdating = True[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Times New Roman]   Application.StatusBar = "Done"
[/FONT][/COLOR]

 
Upvote 0
If you've got non breaking spaces, you can use
Code: 
Sub gazwilliamson()
   With Sheets("Raw Data")
      With .Range("S2", .Range("S" & Rows.Count).End(xlUp))
         .Value = Evaluate("if({1},trim(clean(substitute(" & .Address & ",char(160),""""))))")
      End With
   End With
End Sub
Which should be quicker as it doesn't loop.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Completely cleaning account numbers and storing with a specific format
Replies
2
Views
612
diversification
D
J
  • Solved
TRIM Function
Replies
3
Views
434
Fluff
Fluff
C
  • Solved
Select all cells below a continuous range to delete
Replies
2
Views
451
cyburban
C
H
  • Question
VBA Code to Copy a defined range from a defined list of Worksheets and paste into another sheet
Replies
3
Views
269
Georgiboy
Georgiboy
C
  • Solved
Subscript out of range error
2
Replies
12
Views
524
Domenic
D

Forum statistics

Threads
1,215,051
Messages
6,122,871
Members
449,097
Latest member
dbomb1414

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top