Designating Sheet vs Workbook When Doing Find/Replace

[Paige]

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

 

[Barb Reinhardt]

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.

 

[Paige]

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

 

[Barb Reinhardt]

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

 

[Paige]

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]

Add

Application.Calculation = XLCalculationManual
Application.ScreenUpdating= False

at the beginning

Application.Calculation = XLCalculationAutomatic
Application.ScreenUpdating= True

At the end.

 

[Dave Peterson]

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]

Barb, thanks, but that didn't make a difference unfortunately. There must be
a trick to this, but I'm at my wits end.

 

[Paige]

Hi, Dave. This didn't work either. If the normal Excel Find/Replace dialog
menu shows 'Within: Sheet' and I run my original code, it works okay. But
if it shows 'Within: Workbook' and I run this code, it removes spaces
everywhere. So even tho I select a specific sheet and range to do my
find/replace, it still thinks I mean workbook because that is what the Excel
Find/Replace dialog menu says. Is this strange, or am I strange??!!!

 

[Dave Peterson]

There's no way to change that "within:" option in code.

You'll have to do change it manually.

A nasty feature, huh?

 

[Paige]

Thanks, Dave. That would mean, then, that I basically can't use Find/Replace
in VBA because there's no way to ensure the end user hasn't changed this
field? This does stink.

 

[Dave Peterson]

I've always closed my eyes and hoped for the best.

Thanks, Dave. That would mean, then, that I basically can't use Find/Replace
in VBA because there's no way to ensure the end user hasn't changed this
field? This does stink.

 

[Paige]

Dave, glad to know I'm not a complete idiot. Thanks for the advice!!!