S
Sally Mae
I have a problem with a macro. The macro searches a spreadsheet for thousand
separator blanks and other uneccessary blanks. The contents of the
spreadsheet are pasted onto the spreadsheet. Therefore when running the macro
the program stores all values as text, so I cannot use them in any
calculations. How do you solve this? I have tried recording a macro when
changing the format in excel but it does not work? Pleaase please help me , I
really dont understand it!!!!!Thank you very much!!!
This is the code that I use in a sub for removing the blanks:
Dim WB As Workbook
Dim SH As Worksheet
Dim rng, rCell As Range
Set WB = ActiveWorkbook
Set SH = WB.Sheets("Beräkning")
Set rng = SH.UsedRange
'Set rng = SH.Range("A1100")
For Each rCell In rng.Cells
With rCell
If Not IsEmpty(.Value) Then
If Not UCase(.Value) Like "*[A-Z]*" Then
.Replace What:=" ", Replacement:=""
End If
End If
End With
Next rCell
Any help is ver much appreciated!!
Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
I have also tried (but it does not work when pasting):
separator blanks and other uneccessary blanks. The contents of the
spreadsheet are pasted onto the spreadsheet. Therefore when running the macro
the program stores all values as text, so I cannot use them in any
calculations. How do you solve this? I have tried recording a macro when
changing the format in excel but it does not work? Pleaase please help me , I
really dont understand it!!!!!Thank you very much!!!
This is the code that I use in a sub for removing the blanks:
Dim WB As Workbook
Dim SH As Worksheet
Dim rng, rCell As Range
Set WB = ActiveWorkbook
Set SH = WB.Sheets("Beräkning")
Set rng = SH.UsedRange
'Set rng = SH.Range("A1100")
For Each rCell In rng.Cells
With rCell
If Not IsEmpty(.Value) Then
If Not UCase(.Value) Like "*[A-Z]*" Then
.Replace What:=" ", Replacement:=""
End If
End If
End With
Next rCell
Any help is ver much appreciated!!
Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
I have also tried (but it does not work when pasting):