PDA

View Full Version : Trim not working and moving through a range



the_norm
06-23-2008, 08:28 AM
Hey everybody, I have just started VBA programming and could use some help...

I have a problem with trimming in Excel 2007:

For some reason, the following does not work:

Sub TrimEverything ()
Dim Row NoR as As Long
Dim Column, NoC As Integer

NoR = Application.WorksheetFunction.CountA(ActiveWindow.ActiveSheet.Range("A1:A50000"))
NoC = Application.WorksheetFunction.CountA(ActiveWindow.ActiveSheet.Range("A1:Z1"))

For Row = 1 To NoR

For Column = 1 To NoC
Cells(Row, Column).Value = Trim(Cells(Row, Column).Value)
Next Column

Next Row

End Sub
When I execute it says "Wrong number of arguments or invalid property assignment"... this is giving me headaches since substituting "Trim(Cells(Row, Column).Value)" with "LTrim(RTrim(Cells(Row, Column).Value))" seems to work just fine so the whole deal does not make much sense to me.

Also, I am wondering if this is the best way to move through a worksheet for this kind of substitutions. Is there a way I can do it all at once instead of moving cell by cell? Or perhaps a faster or more proper code or something? I need to move through large quantities of data and do various stuff to it (split data and insert it elsewhere, uppercase, search/replace and such) so speed is important...

Cheers,
the_norm

Bob Phillips
06-23-2008, 08:48 AM
Don't use variables called Row and Column



Sub TrimEverything()
Dim nRow As Long, NoR As Long
Dim nColumn, NoC As Integer

NoR = Application.CountA(ActiveSheet.Range("A1:A50000"))
NoC = Application.CountA(ActiveSheet.Range("A1:Z1"))

For nRow = 1 To NoR

For nColumn = 1 To NoC
Cells(nRow, nColumn).Value = Trim(Cells(nRow, nColumn).Value)
Next nColumn

Next nRow

End Sub