时间戳

首页 > 解决方案 > 我想创建一个宏来删除范围内字符串之后的空格。尝试修剪并没有做任何事情

excel - 我想创建一个宏来删除范围内字符串之后的空格。尝试修剪并没有做任何事情

问题描述

我想将文本“Denver Health”之后的多余空格删除到 A 列中范围的“Denver Health”中。 输入：

A栏

"Denver Health Hospital  "
"Pueblo Hospital "

输出：

A栏

"Denver Health Hospital"
"Pueblo Hospital"

我已经尝试过该代码，但它删除了所有空格

Sub SpaceKiller()
   Worksheets("Sheet2").Columns("A").Replace _
      what:=" ", _
      Replacement:="", _
      SearchOrder:=xlByColumns, _
      MatchCase:=True
End Sub

另一种尝试是

Sub trim()
Dim r As String

r = RTrim(Range("A2:A"))
End Sub

标签： excelvba

解决方案

这将得到尾随空格..

Sub TrimTrailingSpaces()

    Dim LR As Long 'Use Long as Integer can only handle 32,767
    Dim myRng As Range 'I am going to name the used range
    Dim ws As Worksheet 'Declare worksheet as variable
    Dim cll As Range 
    
    Set ws = ThisWorkbook.Worksheets("Sheet2") 'Want to be specific on the worksheet used
    
    LR = ws.Cells(Rows.Count, 1).End(xlUp).Row 'Find the last row in Col A.  Do not include the whole column of over 1 million cells
    
    Set myRng = ws.Range(ws.Cells(2, 1), ws.Cells(LR, 1)) 'Declare the range used
    
    For Each cll In myRng 'cll is a Cell in a Collection of Cells in a Range that we defined
        cll = RTrim(cll) 'Looping through, modify each cell
    Next cll 'Go to the next Cell
    
End Sub

推荐阅读