Defining cells that macro will change
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. |
Defining cells that macro will change
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. |
Defining cells that macro will change
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. |
All times are GMT +1. The time now is 03:00 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com