How do I get a macro to operate only on cells in specific columns?

[Dan E]

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

 

[Bernie Deitrick]

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

 

[Dan E]

Thank you so much, Bernie! I really appreciate it.

Dan

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

 

[Dan E]

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,

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

 

[Bernie Deitrick]

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,
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 E]

OK - thanks very much Bernie. Your help saved my bacon!

Dan