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

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.

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

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


Gord Dibben MS Excel MVP

On Tue, 18 Aug 2009 14:32:01 -0700, John K
wrote:



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

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.

Thanks, that worked.

"Gord Dibben" wrote:

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

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


Gord Dibben MS Excel MVP

On Tue, 18 Aug 2009 14:32:01 -0700, John K
wrote:



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

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.