MS Office Forum / Excel / New Users / February 2005
How do I get a macro to operate only on cells in specific columns?
|
|
Thread rating:  |
Dan E - 27 Feb 2005 00:40 GMT I want to do a Trim on non-formula cells in specific columns. Currently the Trim macro (which works, but is slow because of hitting all cells), looks like this:-
Sub Trim_Text() Dim cell As Range On Error Resume Next For Each cell In ActiveSheet.UsedRange With cell If .HasFormula = False Then .Value = Application.WorksheetFunction.Trim(cell.Value) End If End With Next cell End Sub
and I want it to work only on cells in these columns
Range("B:B,E:E,H:H,K:K,N:N,Q:Q,T:T,Y:Y,AB:AB,AE:AE,AH:AH,AK:AK,AN:AN,AQ:AQ"). _ Select Range("AQ2").Activate
(recorded using the macro recorder), but don't know how the new macro should be written - all help and suggestions very gratefully received. And if there's a way to use SpecialCells in the column range B:AQ, I'd like to see how THAT might be done (columns C, D, F, G, I, J etc contain formulas, and I want to work only on the columns between, which contain data that may need trimming). I have looked at Dave McRitchie's TrimALL macro, but don't understand the syntax etc. sufficiently to be confident in messing with it
:-) TIA,
Dan
 Signature Dan E webbie(removethis)@preferredcountry.com
Bernie Deitrick - 27 Feb 2005 01:01 GMT Dan,
Simply use the intersect method: (all this on one line)
For Each cell In Intersect(ActiveSheet.UsedRange, Range("B:B,E:E,H:H,K:K,N:N,Q:Q,T:T,Y:Y,AB:AB,AE:AE,AH:AH,AK:AK,AN:AN,AQ:AQ"))
Additionally, you could skip the .HasFormula check by using the specialcells property of the range (all of this on one line):
For Each cell In Intersect(ActiveSheet.UsedRange, Range("B:B,E:E,H:H,K:K,N:N,Q:Q,T:T,Y:Y,AB:AB,AE:AE,AH:AH,AK:AK,AN:AN,AQ:AQ")).SpecialCells(xlCellTypeConstants, 23)
Using that would speed things up considerably. Also, you could turn off screenupdating, and turn the calculation mode to manual, and turn off events to speed things up.
Use this at the top of your code: With Application .ScreenUpdating = False .EnableEvents = False .Calculation = xlCalculationManual End With
And this at the bottom: With Application .ScreenUpdating = True .EnableEvents = True .Calculation = xlCalculationAutomatic End With
HTH, Bernie MS Excel MVP
>I want to do a Trim on non-formula cells in specific columns. Currently >the Trim macro (which works, but is slow because of hitting all cells), [quoted text clipped - 31 lines] > > Dan Dan E - 27 Feb 2005 01:21 GMT Thank you so much, Bernie! I really appreciate it.
Dan
> Dan, > [quoted text clipped - 67 lines] >> >> Dan Dan E - 27 Feb 2005 15:38 GMT Bernie, looking at the SpecialCells(xlCellTypeConstants,23) suggestion and looking it up in VBE Help, that seems to select xlErrors, xlLogical, xlNumbers and xlTextValues. Is xlLogical formulas, which I don't want to select? Also, the cells that I want to trim have alphanumeric data only - such as ML, MN, M12 etc., (sometimes with a space or whatever that has crept in my mistake, which causes an error, and that's why I need to trim these cells), which are used in a VLookup to return hours. Would it be best to use SpecialCells(xlCellTypeConstants, 2)?
TIA,
Dan
> Dan, > [quoted text clipped - 67 lines] >> >> Dan Bernie Deitrick - 28 Feb 2005 00:53 GMT Dan,
The 23 means that any constant (ie, any non-formula cell) will be included, though for practical purposes that usually means strings or numbrs. If you know that your constants are always strings, then you can use the 2 as the second parameter (if that is the value that selects only strings).
The other thing that you can do, instead of trimming the values in the cells, is use the worksheetfunction TRIM when you reference the cell value. For example,
=IF(TRIM(A1)="ML","This","That")
will return "This" even if A1 has <space>ML<space>
HTH, Bernie MS Excel MVP
> Bernie, looking at the SpecialCells(xlCellTypeConstants,23) suggestion and > looking it up in VBE Help, that seems to select xlErrors, xlLogical, [quoted text clipped - 80 lines] >>> >>> Dan Dan E - 28 Feb 2005 16:16 GMT OK - thanks very much Bernie. Your help saved my bacon!
Dan
> Dan, > [quoted text clipped - 100 lines] >>>> >>>> Dan
|
|
|