Basically, I have a report which i download from an online website each
day.

The report is automatically formatted in Excel, however, in each column
the first two characters are spaces before it goes onto the number.

I basically want a macro that goes through each row on column A and
deletes these two blank spaces.

Can this be achieved?

If so, how?

Cheers

Brian


--
mccrimmon


------------------------------------------------------------------------
mccrimmon's Profile: http://www.excelforum.com/member.php...fo&userid=6338
View this thread: http://www.excelforum.com/showthread...hreadid=437466

For Each myCell In Range("A1",Range("A65536").End(xlUp)
myCell.Value = Mid(myCell.Value,3,Len(myCell.Value))
Next myCell

HTH,
Bernie
MS Excel MVP


"mccrimmon" wrote in message
...

Basically, I have a report which i download from an online website each
day.

The report is automatically formatted in Excel, however, in each column
the first two characters are spaces before it goes onto the number.

I basically want a macro that goes through each row on column A and
deletes these two blank spaces.

Can this be achieved?

If so, how?

Cheers

Brian


--
mccrimmon


------------------------------------------------------------------------
mccrimmon's Profile: http://www.excelforum.com/member.php...fo&userid=6338
View this thread: http://www.excelforum.com/showthread...hreadid=437466

Dim myCell as Range
For Each myCell In Range("A1",Cells(row.count,1).End(xlUp))
myCell.Value = Ltrim(myCell.Value)
Next myCell

would be another way. to trim both leading and trailing spaces, replace
Ltrim with Trim

--
Regards,
Tom Ogilvy



"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
For Each myCell In Range("A1",Range("A65536").End(xlUp)
myCell.Value = Mid(myCell.Value,3,Len(myCell.Value))
Next myCell

HTH,
Bernie
MS Excel MVP


"mccrimmon" wrote
in message
...

Basically, I have a report which i download from an online website each
day.

The report is automatically formatted in Excel, however, in each column
the first two characters are spaces before it goes onto the number.

I basically want a macro that goes through each row on column A and
deletes these two blank spaces.

Can this be achieved?

If so, how?

Cheers

Brian


--
mccrimmon


------------------------------------------------------------------------
mccrimmon's Profile:
http://www.excelforum.com/member.php...fo&userid=6338
View this thread:
http://www.excelforum.com/showthread...hreadid=437466

Tom,

Down-loaded files often contain char 160 (or other non-printing character) as a space, which TRIM
doesn't deal with properly, which is why I went to the MID solution.

Another solution could be:

Range("A:A").Replace What:=Chr(160) & Chr(160), Replacement:="", LookAt:=xlPart

with the 160 replaced with the proper code, unless there were interior double spaces that were
important.

HTH,
Bernie
MS Excel MVP


"Tom Ogilvy" wrote in message ...
Dim myCell as Range
For Each myCell In Range("A1",Cells(row.count,1).End(xlUp))
myCell.Value = Ltrim(myCell.Value)
Next myCell

would be another way. to trim both leading and trailing spaces, replace
Ltrim with Trim

--
Regards,
Tom Ogilvy



"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
For Each myCell In Range("A1",Range("A65536").End(xlUp)
myCell.Value = Mid(myCell.Value,3,Len(myCell.Value))
Next myCell

HTH,
Bernie
MS Excel MVP


"mccrimmon" wrote
in message
...

Basically, I have a report which i download from an online website each
day.

The report is automatically formatted in Excel, however, in each column
the first two characters are spaces before it goes onto the number.

I basically want a macro that goes through each row on column A and
deletes these two blank spaces.

Can this be achieved?

If so, how?

Cheers

Brian


--
mccrimmon


------------------------------------------------------------------------
mccrimmon's Profile:
http://www.excelforum.com/member.php...fo&userid=6338
View this thread:
http://www.excelforum.com/showthread...hreadid=437466

Hi Tom,
I have a similar question about spaces before the text.
=IF(ISBLANK(A1),"",IF(LEFT(A1,2)=" ",MID(A1,3,LEN(A1)),A1))

I'm using this formula to remove just the first 2 spaces only within the
text. How do I convert this to code? I've tried the Replace function
but it doesn't work right. When I enter just 2 spaces, it deletes all
spaces prior to the text. And I wanted to keep the remaining spaces
after the first 2 spaces.

I found this previously posted solution from Mr. Benson. I also like
your code which includes all of column A:

Sub RemoveSpaces()
Dim Rng As Range, Cell As Range
Set Rng = ActiveSheet.UsedRange
For Each Cell In Rng
If Not IsEmpty(Cell) _
And Len(Cell) = 1 _
And Right(Cell, 1) = Chr(32) _
And Not Cell.HasFormula Then _
Cell = RTrim(Cell)
Next
End Sub

How do you convert this to just remove the first 2 spaces from the left?
If the cell is empty, ignore and move down the list (all within column
A). Do I have to factor in the Chr(160) also?

Thanks so much,
Ricky

*** Sent via Developersdex http://www.developersdex.com ***

Do it in place - alter the cells themselves?

Sub Remove2LeftBlanks()
Dim cell as Range, rng as Range
Set rng = Range(cells(1,1),cells(rows.count).End(xlup))
for each cell in rng
if Left(cell.Value = " ") then
cell.Value = Mid(cell.Value,3,len(cell)-2)
end if
Next
End Sub

to put the results in the cell to the right:

Sub ReturnAllbut2LeftBlanks()
Dim cell as Range, rng as Range
Set rng = Range(cells(1,1),cells(rows.count).End(xlup))
for each cell in rng
if Left(cell.Value = " ") then
cell.Offset(0,1).Value = Mid(cell.Value,3,len(cell)-2)
end if
Next
End Sub

--
Regards,
Tom Ogilvy



"Ricky Pang" wrote in message
...
Hi Tom,
I have a similar question about spaces before the text.
=IF(ISBLANK(A1),"",IF(LEFT(A1,2)=" ",MID(A1,3,LEN(A1)),A1))

I'm using this formula to remove just the first 2 spaces only within the
text. How do I convert this to code? I've tried the Replace function
but it doesn't work right. When I enter just 2 spaces, it deletes all
spaces prior to the text. And I wanted to keep the remaining spaces
after the first 2 spaces.

I found this previously posted solution from Mr. Benson. I also like
your code which includes all of column A:

Sub RemoveSpaces()
Dim Rng As Range, Cell As Range
Set Rng = ActiveSheet.UsedRange
For Each Cell In Rng
If Not IsEmpty(Cell) _
And Len(Cell) = 1 _
And Right(Cell, 1) = Chr(32) _
And Not Cell.HasFormula Then _
Cell = RTrim(Cell)
Next
End Sub

How do you convert this to just remove the first 2 spaces from the left?
If the cell is empty, ignore and move down the list (all within column
A). Do I have to factor in the Chr(160) also?

Thanks so much,
Ricky

*** Sent via Developersdex http://www.developersdex.com ***

Hi Tom,
Thank-you for coming to my rescue yet again. When I ran both codes, I
got this same error message: Compile Error: Argument not optional.
While debugging, the [left] is highlighted (which is the 5th line down
of the Remove2LeftBlanks code).

My apologies, I'm unsure what you meant by Do it in place.

I hope to see your reply again.

Much appreciated.

Ricky


*** Sent via Developersdex http://www.developersdex.com ***

Mac,

Try this:

Sub RemovePreSpaces()
Dim rng As Range, c As Range

Set rng = ActiveSheet.UsedRange
For Each c In rng
c.Value = Mid(c, 3)
Next

End Su

--
david
-----------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...fo&userid=2064
View this thread: http://www.excelforum.com/showthread.php?threadid=43746

You don't really need a macro for this... There is find and replace which you
can run against the column replacing all instances of double blank with
nothing. If that won't work for you there is a trim() or ltrim() function
right in Excel that you can use... If you need it to be a macro then you can
try this

Sub RemoveBlank()
dim rngCurrent as range
dim rngToSearch as range
dim wks as worksheet

set wks = activesheet
set rngToSearch = intersect(wks.Range("A:A"), wks.usedrange)
for each rngCurrent in rngToSearch
rngCurrent.value = ltrim(rngcurrent.value)
next wksCurrent

end sub

--
HTH...

Jim Thomlinson


"mccrimmon" wrote:


Basically, I have a report which i download from an online website each
day.

The report is automatically formatted in Excel, however, in each column
the first two characters are spaces before it goes onto the number.

I basically want a macro that goes through each row on column A and
deletes these two blank spaces.

Can this be achieved?

If so, how?

Cheers

Brian


--
mccrimmon


------------------------------------------------------------------------
mccrimmon's Profile: http://www.excelforum.com/member.php...fo&userid=6338
View this thread: http://www.excelforum.com/showthread...hreadid=437466

ltrim is a vba function--it's not built into excel.

And edit|replace could cause havoc depending on what else is in those cells.

Jim Thomlinson wrote:

You don't really need a macro for this... There is find and replace which you
can run against the column replacing all instances of double blank with
nothing. If that won't work for you there is a trim() or ltrim() function
right in Excel that you can use... If you need it to be a macro then you can
try this

Sub RemoveBlank()
dim rngCurrent as range
dim rngToSearch as range
dim wks as worksheet

set wks = activesheet
set rngToSearch = intersect(wks.Range("A:A"), wks.usedrange)
for each rngCurrent in rngToSearch
rngCurrent.value = ltrim(rngcurrent.value)
next wksCurrent

end sub

--
HTH...

Jim Thomlinson

"mccrimmon" wrote:


Basically, I have a report which i download from an online website each
day.

The report is automatically formatted in Excel, however, in each column
the first two characters are spaces before it goes onto the number.

I basically want a macro that goes through each row on column A and
deletes these two blank spaces.

Can this be achieved?

If so, how?

Cheers

Brian


--
mccrimmon


------------------------------------------------------------------------
mccrimmon's Profile: http://www.excelforum.com/member.php...fo&userid=6338
View this thread: http://www.excelforum.com/showthread...hreadid=437466



--

Dave Peterson