This code is suppose to remove excess spaces from the specified range.
However, it sometimes thinks I mean to do this within the entire workbook,
versus the range on the specified sheet. Have not been able to figure out
how to adjust this so that it knows to only do this within a specific range.
Can someone please help?

Worksheets("MMS for CP").Range(Range("G3"), Range("G65536").End(xlUp)).Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

What's the intent of this line?

Worksheets("MMS for CP").Range(Range("G3"), Range("G65536").End(xlUp)).Select

I'm guessing that's part of the problem.
--
HTH,
Barb Reinhardt



"Paige" wrote:

This code is suppose to remove excess spaces from the specified range.
However, it sometimes thinks I mean to do this within the entire workbook,
versus the range on the specified sheet. Have not been able to figure out
how to adjust this so that it knows to only do this within a specific range.
Can someone please help?

Worksheets("MMS for CP").Range(Range("G3"), Range("G65536").End(xlUp)).Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

It's suppose to tell it what range to do the find/replace.

"Barb Reinhardt" wrote:

What's the intent of this line?

Worksheets("MMS for CP").Range(Range("G3"), Range("G65536").End(xlUp)).Select

I'm guessing that's part of the problem.
--
HTH,
Barb Reinhardt



"Paige" wrote:

This code is suppose to remove excess spaces from the specified range.
However, it sometimes thinks I mean to do this within the entire workbook,
versus the range on the specified sheet. Have not been able to figure out
how to adjust this so that it knows to only do this within a specific range.
Can someone please help?

Worksheets("MMS for CP").Range(Range("G3"), Range("G65536").End(xlUp)).Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

Is the range from G3 to the last row of data in G?

This may not be the most elegant, but it works

Sub Test()
Dim myWS As Worksheet
Dim myRange As Range
Dim r As Range
Dim lRow As Long

Set myWS = Worksheets("MMS for CP")
lRow = myWS.Cells(myWS.Rows.Count, "G").End(xlUp).Row
Set myRange = myWS.Range("G3").Resize(lRow - 3 + 1, 1)

For Each r In myRange
r.Value = Replace(r.Value, " ", "")
Next r

End Sub

--
HTH,
Barb Reinhardt



"Paige" wrote:

It's suppose to tell it what range to do the find/replace.

"Barb Reinhardt" wrote:

What's the intent of this line?

Worksheets("MMS for CP").Range(Range("G3"), Range("G65536").End(xlUp)).Select

I'm guessing that's part of the problem.
--
HTH,
Barb Reinhardt



"Paige" wrote:

This code is suppose to remove excess spaces from the specified range.
However, it sometimes thinks I mean to do this within the entire workbook,
versus the range on the specified sheet. Have not been able to figure out
how to adjust this so that it knows to only do this within a specific range.
Can someone please help?

Worksheets("MMS for CP").Range(Range("G3"), Range("G65536").End(xlUp)).Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

Hi, Barb. The range is from G3 to the last row of data in G. I'm trying not
to use the "for each....next" method because there are so many rows and it
adds too much processing time. That's why I was trying to do Find/Replace,
but can't seem to tell it to make sure and only do that within the specified
range and not within the entire workbook. If you go to Edit | Replace in
Excel, the Find and Replace dialog box gives you an option to do this within
workbook or sheet, but I don't know how to do it with code.

"Barb Reinhardt" wrote:

Is the range from G3 to the last row of data in G?

This may not be the most elegant, but it works

Sub Test()
Dim myWS As Worksheet
Dim myRange As Range
Dim r As Range
Dim lRow As Long

Set myWS = Worksheets("MMS for CP")
lRow = myWS.Cells(myWS.Rows.Count, "G").End(xlUp).Row
Set myRange = myWS.Range("G3").Resize(lRow - 3 + 1, 1)

For Each r In myRange
r.Value = Replace(r.Value, " ", "")
Next r

End Sub

--
HTH,
Barb Reinhardt



"Paige" wrote:

It's suppose to tell it what range to do the find/replace.

"Barb Reinhardt" wrote:

What's the intent of this line?

Worksheets("MMS for CP").Range(Range("G3"), Range("G65536").End(xlUp)).Select

I'm guessing that's part of the problem.
--
HTH,
Barb Reinhardt



"Paige" wrote:

This code is suppose to remove excess spaces from the specified range.
However, it sometimes thinks I mean to do this within the entire workbook,
versus the range on the specified sheet. Have not been able to figure out
how to adjust this so that it knows to only do this within a specific range.
Can someone please help?

Worksheets("MMS for CP").Range(Range("G3"), Range("G65536").End(xlUp)).Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

Add

Application.Calculation = XLCalculationManual
Application.ScreenUpdating= False

at the beginning

Application.Calculation = XLCalculationAutomatic
Application.ScreenUpdating= True

At the end.
--
HTH,
Barb Reinhardt



"Paige" wrote:

Hi, Barb. The range is from G3 to the last row of data in G. I'm trying not
to use the "for each....next" method because there are so many rows and it
adds too much processing time. That's why I was trying to do Find/Replace,
but can't seem to tell it to make sure and only do that within the specified
range and not within the entire workbook. If you go to Edit | Replace in
Excel, the Find and Replace dialog box gives you an option to do this within
workbook or sheet, but I don't know how to do it with code.

"Barb Reinhardt" wrote:

Is the range from G3 to the last row of data in G?

This may not be the most elegant, but it works

Sub Test()
Dim myWS As Worksheet
Dim myRange As Range
Dim r As Range
Dim lRow As Long

Set myWS = Worksheets("MMS for CP")
lRow = myWS.Cells(myWS.Rows.Count, "G").End(xlUp).Row
Set myRange = myWS.Range("G3").Resize(lRow - 3 + 1, 1)

For Each r In myRange
r.Value = Replace(r.Value, " ", "")
Next r

End Sub

--
HTH,
Barb Reinhardt



"Paige" wrote:

It's suppose to tell it what range to do the find/replace.

"Barb Reinhardt" wrote:

What's the intent of this line?

Worksheets("MMS for CP").Range(Range("G3"), Range("G65536").End(xlUp)).Select

I'm guessing that's part of the problem.
--
HTH,
Barb Reinhardt



"Paige" wrote:

This code is suppose to remove excess spaces from the specified range.
However, it sometimes thinks I mean to do this within the entire workbook,
versus the range on the specified sheet. Have not been able to figure out
how to adjust this so that it knows to only do this within a specific range.
Can someone please help?

Worksheets("MMS for CP").Range(Range("G3"), Range("G65536").End(xlUp)).Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

Are you having trouble with that first line of code?

If MMS for CP isn't the activesheet, then I would expect that line to cause an
error.

with worksheets("mms for cp")
with .range("g3",.cells(.rows.count,"G").end(xlup))
.cells.replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
end with
end with

Those unqualified ranges will refer to the activesheet (if the code is in a
general module)--or the sheet that owns the code if the code is behind a
worksheet.



Paige wrote:

This code is suppose to remove excess spaces from the specified range.
However, it sometimes thinks I mean to do this within the entire workbook,
versus the range on the specified sheet. Have not been able to figure out
how to adjust this so that it knows to only do this within a specific range.
Can someone please help?

Worksheets("MMS for CP").Range(Range("G3"), Range("G65536").End(xlUp)).Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

--

Dave Peterson