VBA to remove all spaces inside specific worksheet within range

Question

Need to have below code modified. It removes all spaces inside specific worksheet (this time we call it "Paste Data") from all cells. It can be run from any other worksheets. I would like to have option to set a range only. For example it will remove all spaces from worksheet "Paste Data" from F10:L11. Any ideas?

Public Sub Remove_unwanted_spaces()

Dim sht As Worksheet
Dim fnd As Variant
Dim rplc As Variant

fnd = " "
rplc = ""

'Store a specific sheet to a variable
  Set sht = Sheets("Paste DATA")

'Perform the Find/Replace All
  sht.Cells.Replace what:=fnd, Replacement:=rplc, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False

End Sub

If you are attempting to replace leading and trailing spaces only, use the Trim function — Darrell H
Welcome to SO! When you post a question, you should show some minimums... as any effort. What did you try? Any research? Just using the Macro recorder you will get the point to reach to your solution... — David García Bodego

senior_freshman senior_freshman · Accepted Answer · 2020-12-04T13:19:27

Take a look at this Range.Replace

You then have to change your Code, starting with:

Range("A1:Z3000").Replace what:=fnd, Replacement:=rplc, _ "and so on and so on

VBA to remove all spaces inside specific worksheet within range

1 Answers