Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need help with excel macro

Status
Not open for further replies.

ncolsgk

IS-IT--Management
Apr 2, 2007
98
US
I have a spreadsheet with 30,932 rows. Column C has a ton of cells that have blank spaces at the end of the text which I would like to delete. I know that I need to include the Trim function but how do I setup the macro so that it goes from C1, to C2, to C3, to C4, etc?

This is what I have so far:

Code:
Application.ActiveCell = Trim(Application.ActiveCell)


 
If you select all cells you wish to trim, then you could run the following

Dim curcell As Object
For Each curcell In Selection
curcell.Value = Trim(curcell.Value)
Next curcell

Member- AAAA Association Against Acronym Abusers
 
So then it would be this?

Code:
Sub delete_spaces()
'
' select_cells Macro
' Macro recorded 4/13/2007 by me
'
'
    
Range("C2:C30104").Select
Dim curcell As Object
For Each curcell In Selection
curcell.Value = Trim(curcell.Value)
Next curcell 

End Sub
 




Yes, but...

Either select the range and run the code without the SELECT statement or...
Code:
Dim curcell As Object
For Each curcell In Range("C2:C30104")
curcell.Value = Trim(curcell.Value)
Next curcell
in either case you do NOT need to use the Select Method in your code.

Skip,

[glasses] [red][/red]
[tongue]
 
Sweet jebus that worked! Thanks!

Now I just need help with one more and I will be a happy camper.

I would like to select all cells in column A (A2:A30104) and delete the hyphen/dash at the end.

So instead of:

210-20057-56310-

It will be:

210-20057-56310
 


Is there ALWAYS a trailing DASH?

If so, you can use the Left & Len functions.



Skip,

[glasses] [red][/red]
[tongue]
 
Not all cells have a trailing dash.
 



But if there is one there is ONLY ONE?
Code:
Dim curcell As Object
For Each curcell In Range("C2:C30104")
  if right(curcell.value,1) = "-" then
    curcell.value = left(curcell.value, len(curcell.value)-1)
  end if
Next curcell

Skip,

[glasses] [red][/red]
[tongue]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top