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
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