-1
Set r = Worksheets("Sheet1").Columns("A").Find(What:="  ")
     If r Is Nothing Then
            MsgBox "done"

\ End If End Sub

mahesh gujar
  • 3
  • 5
  • 1
    what code have you already tried? – braX Jul 12 '17 at 12:23
  • https://stackoverflow.com/questions/30768072/fastest-way-to-remove-extra-spaces-more-than-1-from-a-large-range-of-cells-u These option i tried but not helpful for me.. – mahesh gujar Jul 12 '17 at 12:24
  • 1
    Edit your post to include _exactly_ the code you've tried and describe _exactly_ what did and did not work and any error messages you may have received. Otherwise we're just guessing or feeling like a free code-writing service. – FreeMan Jul 12 '17 at 12:28
  • The code you have provided only shows a message when there is no space found. Where is the `Else` part? – trincot Jul 12 '17 at 12:38
  • The lack of information makes it hard for us to help you. It also feels like you didn't put much research in your issue before posting the question – MisterBic Jul 12 '17 at 12:39
  • Not any specific code am trying..can any one has any code that they used to remove space so will try to overcome my problem.. – mahesh gujar Jul 12 '17 at 12:40
  • @Antoine.D i did actually but that not helping with my problem so at the end i posted.. – mahesh gujar Jul 12 '17 at 12:41

1 Answers1

1

the below code removes any spaces in the text in col A. Hope this is what you were looking for. Amend the code as necessary. I have done the changes so that it works for the entire sheet. accept the answer by clicking accept if it resolves your requirement

   Sub teste()

    Dim UsedRng As Range
    Dim FirstRow As Long, LastRow As Long, FirstCol As Long, LastCol As Long

    Set UsedRng = ActiveSheet.UsedRange

    FRow = UsedRng(1).Row
    FCol = UsedRng(1).Column
    lRow = UsedRng(UsedRng.Cells.Count).Row
    Lcol = UsedRng(UsedRng.Cells.Count).Column

For X = FRow To lRow

For Y = FCol To Lcol
        temp = Cells(X, Y).Value
        tempC = (Trim(temp))
        Cells(X, Y).Value = tempC
        Next Y
Next X
End Sub
Apurv Pawar
  • 424
  • 3
  • 11