PDA

View Full Version : Trim cells in variable range



gtrdude485
07-02-2008, 02:20 PM
I'm trying to trim all cells in in a variable range. Not all cells will have values but I do know that Column A and Row 1 will have the maximum number of rows/columns of the range. I am having trouble defining the range. Code is below:


Sub Top()
Dim Ncol As Long, Nrow As Long, nLoop1 As Long
Dim colEnd As Range, rowEnd As Range, rCell As Range

Set colEnd = Range("IV1").End(xlToLeft)
Ncol = Range("a1", colEnd).Columns.Count
Set rowEnd = Range("A65536").End(xlUp)
Nrow = Range("A1", rowEnd).Rows.Count

For nLoop1 = 1 To Ncol
For Each rCell In Range("A1:IV65536").Column(nLoop).Cells
rCell.value = Trim(rCell.value)
Next rCell
Next nLoop1

End Sub

JimmyTheHand
07-02-2008, 02:53 PM
Sub Top()
Dim rngTop As Range, rngLeft As Range, rCell As Range

Set rngTop = Range("A1", Cells(1, Columns.Count).End(xlToLeft))
Set rngLeft = Range("A1", Range("A" & Rows.Count).End(xlUp))

For Each rCell In Intersect(rngTop.EntireColumn, rngLeft.EntireRow)
rCell.Value = Trim(rCell.Value)
Next rCell

End Sub