Defining cells that macro will change

J

John K

I just installed a macro and want to have the range of cells that it will
update to be defined in the macro. Here is the macro:

Sub TRIMALL_alternate()
'Dave Peterson, programming, 2002-08-17
'http://google.com/[email protected]
Dim myrange As Range
Dim myCol As Range

Set myrange = Intersect(ActiveSheet.UsedRange, Selection)
If myrange Is Nothing Then Exit Sub
Application.ScreenUpdating = False

myrange.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

For Each myCol In myrange.Columns
If Application.CountA(myCol) > 0 Then
myCol.TextToColumns Destination:=myCol(1), _
DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)
End If
Next myCol
Application.ScreenUpdating = True
End Sub

I want to have it select D4:L178 but I can't figure out where to define
this. If I select the range and run the macro it works. I would appreciate
any help.
 
G

Gord Dibben

Change Set myrange = Intersect(ActiveSheet.UsedRange, Selection) to

Set myrange = ActiveSheet.Range("D4:L178")


Gord Dibben MS Excel MVP
 
J

John K

Thanks, that worked.

Gord Dibben said:
Change Set myrange = Intersect(ActiveSheet.UsedRange, Selection) to

Set myrange = ActiveSheet.Range("D4:L178")


Gord Dibben MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top