Macro Help - Deleting two characters per line!
 

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

Macro Help - Deleting two characters per line!
 

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

Macro Help - Deleting two characters per line!
 

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

Macro Help - Deleting two characters per line!
 

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

Macro Help - Deleting two characters per line!
 

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

Macro Help - Deleting two characters per line!
 

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

Macro Help - Deleting two characters per line!
 

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

Macro Help - Deleting two characters per line!
 

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

Macro Help - Deleting two characters per line!
 

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

Macro Help - Deleting two characters per line!
 

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

Macro Help - Deleting two characters per line!
 

Highlight LEFT and hit F1. You will see that Left takes 2 arguments. I
omitted the second argument to show the number of cells from left to use

Left(cell.Value = " ")

should be

Left(cell.Value,2) = " "

where the "" contain two spaces.

My typo.

--
Regards,
Tom Ogilvy


"Ricky Pang" wrote:

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

Macro Help - Deleting two characters per line!
 

Hi Tom,
It worked! I made a slight change to the "Set rng" line so that the
code would run through the entire column. I hope this right (it does
work) but is this how you would write the change?
As importantly, I'd like to put an If..then.. checker on the page so
that if this code has already been activated once already, it would stop
executing to prevent the deletions of the remaining spaces that I do
want to keep.

Something to the effect of; the last action of this code is to put a
word in cell "D1" such as "Spaces already deleted".
Then, if anyone runs it again by accident, the first thing that the code
does is to check if "D1" has the word "Spaces already deleted"
indicated. If it does, then code stops. If not, then code will delete
the empty spaces.

Sub Remove2LeftBlanks()
Dim cell As Range, rng As Range
Set rng = Range("A:A")
For Each cell In rng
If Left(cell.Value, 2) = " " Then
cell.Value = Mid(cell.Value, 3, Len(cell) - 2)
End If
Next
End Sub

Your assistance is greatly appreciated.

Thanks again,
Ricky

P.S. I'm trying to go through your Msgbox Search and Replace code.
Something is not quite working. I'll get back to you very shortly.


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

Macro Help - Deleting two characters per line!
 

Sub Remove2LeftBlanks()
Dim cell As Range, rng As Range
on Error Resume Next
set rng = Activesheet.Names("ABC").ReferstoRange
On error resume next
if rng is nothing then
Set rng = Range(Cells(1,1),Cells(rows.count,1).End(xlup))
For Each cell In rng
If Left(cell.Value, 2) = " " Then
cell.Value = Mid(cell.Value, 3, Len(cell) - 2)
End If
Next
' create the named range "ABC" to
' indicate the macro has been run.
rng.name = "'" & activesheet.Name & "'!ABC"
else
Msgbox "Already been run"
End if
End Sub

--
Regards,
Tom Ogilvy

"Ricky Pang" wrote in message
...
Hi Tom,
It worked! I made a slight change to the "Set rng" line so that the
code would run through the entire column. I hope this right (it does
work) but is this how you would write the change?
As importantly, I'd like to put an If..then.. checker on the page so
that if this code has already been activated once already, it would stop
executing to prevent the deletions of the remaining spaces that I do
want to keep.

Something to the effect of; the last action of this code is to put a
word in cell "D1" such as "Spaces already deleted".
Then, if anyone runs it again by accident, the first thing that the code
does is to check if "D1" has the word "Spaces already deleted"
indicated. If it does, then code stops. If not, then code will delete
the empty spaces.

Sub Remove2LeftBlanks()
Dim cell As Range, rng As Range
Set rng = Range("A:A")
For Each cell In rng
If Left(cell.Value, 2) = " " Then
cell.Value = Mid(cell.Value, 3, Len(cell) - 2)
End If
Next
End Sub

Your assistance is greatly appreciated.

Thanks again,
Ricky

P.S. I'm trying to go through your Msgbox Search and Replace code.
Something is not quite working. I'll get back to you very shortly.


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

Macro Help - Deleting two characters per line!
 

Hi Tom,
It worked very nicely. Thank-you very much. This is just the added
security feature that I'm looking for.

Much appreciated,
Ricky



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