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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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

T
  • Solved
Help with VBA Code in Excel
Replies
2
Views
306
tixarah
T
D
  • Question Question
Completely cleaning account numbers and storing with a specific format
Replies
2
Views
1K
diversification
D
P
  • Question Question
TypeScript (or other solutions) for modifying given list of cells (and similar problems)
Replies
0
Views
93
Pingaware
P
M
  • Question Question
Split Excel Workbook to separate .xlsx files for each unique value in Column A, also copy worksheet for Drop Down reference
Replies
9
Views
454
Alex Blakenburg
A
M
  • Solved
Deleting rows from a Range variable
Replies
4
Views
273
Umma123
U

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

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