Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / February 2005

Tip: Looking for answers? Try searching our database.

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

Thread view: 
Enable EMail Alerts  Start New Thread
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.