VBA

  • Thread starter Thread starter dipsy
  • Start date Start date
D

dipsy

Hi! How can I remove via VBA code any extra characters
like - Enter (Chr(10)), +, - and just keep the text that
was originally present in the cell.

TIA
 
I'm not sure how you would know what was in the cell originally.

If I were cleaning a bunch of cells, I'd record a macro while I did it manually
and modify it.

I got something like:

Selection.Replace What:="+", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

(I replaced + with " " (spacebar).)

And change the "+" to chr(10) to get the alt-enter.

You could wrap it in a loop to get all your characters. And I think some
versions of excel will do a replace all if you only have one cell selected. (So
I included another cell--just in case.)

Option Explicit
Sub testme()

Dim myRng As Range
Dim myChars As Variant
Dim iCtr As Long

myChars = Array(Chr(10), "+", "-")

If Selection.Cells.Count = 1 Then
Set myRng = Union(Selection, _
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1))
Else
Set myRng = Selection
End If

For iCtr = LBound(myChars) To UBound(myChars)
myRng.Replace What:=myChars(iCtr), Replacement:=" ", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Next iCtr

End Sub

(I just tried using the selection against a single cell in xl2002 and it only
changed that cell. So maybe my memory is incorrect for all versions!)
 
Dave:

Thank you so much for the feedback. I modified the code
to do what I needed. I did have a q though:

If Selection.Cells.Count = 1 Then
Set myRng = Union(Selection, _
ActiveSheet.Cells.SpecialCells _
(xlCellTypeLastCell).Offset(1, 1))

What does this line of code do?

Thanks a lot again!
-----Original Message-----
I'm not sure how you would know what was in the cell originally.

If I were cleaning a bunch of cells, I'd record a macro while I did it manually
and modify it.

I got something like:

Selection.Replace What:="+", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

(I replaced + with " " (spacebar).)

And change the "+" to chr(10) to get the alt-enter.

You could wrap it in a loop to get all your characters. And I think some
versions of excel will do a replace all if you only have one cell selected. (So
I included another cell--just in case.)

Option Explicit
Sub testme()

Dim myRng As Range
Dim myChars As Variant
Dim iCtr As Long

myChars = Array(Chr(10), "+", "-")

If Selection.Cells.Count = 1 Then
Set myRng = Union(Selection, _
ActiveSheet.Cells.SpecialCells
Click to expand...
(xlCellTypeLastCell).Offset(1, 1))
 
That was where my memory failed me. I though that with some versions of excel,
that if you only had one cell selected and did an Edit|Replace (via code), then
it would update all the cells that matched.

I tested in xl2002 and it didn't behave that way--but I could have sworn xl97
did it this way.

So that code says if you only have one cell selected, then select an empty cell
(outside the usedrange of your worksheet) and add it to your selection.

Then doing the Edit|Replace (via code) knows to limit it to just those selected
cells.

Since I don't have xl97/xl2k to test and I didn't know what version of xl you're
using, I figured better safe than sorry.

Maybe you could test it both ways and post back your results--include your
version of xl, too.

Thanks,
 
Hi Dave:

Thanks for explaining that code. I am using Excel 2000.

This is the code that I used. If there is a mistake in
the code, I would appreciate your feedback.

Thanks.

Dim myRng As Range
Dim myChars As Variant
Dim iCtr As Long
Dim c As Long

myChars = Array(Chr(10), "+", "-")

For c = Cells(1, Columns.Count).End(xlToLeft).Column
To 1 Step -1

Set myRng = Cells(1, c)

For iCtr = LBound(myChars) To UBound(myChars)
myRng.Replace What:=myChars(iCtr),
Replacement:=" ", _
LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False
Next iCtr

Next c
 
I'm not sure if it's a mistake or if that's what you wanted to do.

But by using cells(1,c), you're looking at exactly one cell--the topcell in each
column (row 1).

If that's what you meant, then you could drop the looping and just do an
Edit|replace against row 1.

Something like:

Option Explicit
Sub testme01()
Dim myRng As Range
Dim myChars As Variant
Dim iCtr As Long

myChars = Array(Chr(10), "+", "-")

Set myRng = Rows(1)
For iCtr = LBound(myChars) To UBound(myChars)
myRng.Replace What:=myChars(iCtr), Replacement:=" ", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Next iCtr

End Sub

And the way it's written now (and if it's placed in a general module), it runs
against the activesheet.

I like to be a little more specific--just so I don't activate the wrong sheet
and run the macro by mistake.

By changing:
Set myRng = Rows(1)
to
Set myRng = activesheet.Rows(1)
(to use the activesheet)

or to
Set myRng = worksheets("sheet1").Rows(1)
to be more explicit.

And if you really wanted to get the whole worksheet, you could:

Set myRng = activesheet.usedrange
(just in case that's what you really intended)
 
Back
Top