Solved: Removing Leading Spaces

[vzachin]

Hi,

I have data that is imported into Excel beginning in A5 and down. The data that is imported has a leading space which I remove with the following code:
[vba]
Sheets("Sheet1").Select
Range("A5:A5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range("A5").Select
[/vba]

If the imported data exceeds row 65536, then the data is entered in every 4 columns up to Column IO. (A5,E5,I5...IO5)
How can I re-write the above code to work if there is any subsequent data after A5? That is to remove the leading space if there is data in the subsequent columns.


thanks
zach

[johnske]

[vba]
Option Explicit

Sub Try()
On Error Resume Next
With Sheets("Sheet1")
.Range("A5", .Cells.SpecialCells(xlCellTypeLastCell).Address).Replace " ", ""
.Range("A5").Select
End With
End Sub

[/vba]

[vzachin]

hi John,

thanks! that works, don't quite understand how & why though.

zach

[johnske]

The Range property requires a string that can be equated with at least one cell address inside the brackets(), when there are two comma separated strings inside the brackets, let us say A1 and C10 {written as Range("A1", "C10")} it's an instruction to operate on all the cells in the range A1 to C10 and is completely equivalent to Range("A1:C10").

Now Cells.SpecialCells(xlCellTypeLastCell).Address is the last cell in the used range, so we're operating on the range Range("A5", LastCell).

It's actually a waste of time to do it in the following manner, but simply to help you to understand it better we can do the same thing by breaking it into smaller steps and create an artificial variable called LastCell e.g...
[vba]
Option Explicit
'
Sub Try2()
'
Dim LastCell As String
'
On Error Resume Next
With Sheets("Sheet1")
LastCell = .Cells.SpecialCells(xlCellTypeLastCell).Address
.Range("A5", LastCell).Replace " ", ""
On Error GoTo 0
.Range("A5").Select
End With
'
End Sub

[/vba]It's also unnecessary to leave in all the 'selecting' as you've done - that's only done when you make a recorded macro because the recorder's blindly recording all your key-strokes and mouse-clicks

[vzachin]

hi John,

Thanks for the explanation. Now I have a better understanding.

zach