I have a sheet that in Row 1 has text. Within that text, there is also a
character/s (maybe carriage return, not sure) that is messing everything up.

I need a way to automate (e.g. macro) that row only having "valid
characters." i'm not sure if i would use an ascii chart or what to weed them
out. i can only have characters 32 - 126, in an ascii chart. I can send the
sheet in question.

thanks,
mark

Hi Mark,

in say A2
= Clean(A1)
copy across, then copy row 2 and paste special values

Regards,
Peter T




"mvyvoda" wrote in message
...
I have a sheet that in Row 1 has text. Within that text, there is also a
character/s (maybe carriage return, not sure) that is messing everything
up.

I need a way to automate (e.g. macro) that row only having "valid
characters." i'm not sure if i would use an ascii chart or what to weed
them
out. i can only have characters 32 - 126, in an ascii chart. I can send
the
sheet in question.

thanks,
mark

This is perfect! So, i'm newish to VBA, normally I would record a macro, then
go from there. I'm sure there's an easier way to this clean function to an
entire row, that in the macro, i wouldn't have to use another row. i'd
imagine just a place holder variable.

can someone (PT?) help with this as well?

thanks so much,
-m

"Peter T" wrote:

Hi Mark,

in say A2
= Clean(A1)
copy across, then copy row 2 and paste special values

Regards,
Peter T




"mvyvoda" wrote in message
...
I have a sheet that in Row 1 has text. Within that text, there is also a
character/s (maybe carriage return, not sure) that is messing everything
up.

I need a way to automate (e.g. macro) that row only having "valid
characters." i'm not sure if i would use an ascii chart or what to weed
them
out. i can only have characters 32 - 126, in an ascii chart. I can send
the
sheet in question.

thanks,
mark

Dim rng as Range, cell as Range
set rng = ActiveCell.EntireRow
for each cell in rng
if not isempty(cell) then
cell = application.Clean(cell)
end if
Next

--
Regards,
Tom Ogilvy


"mvyvoda" wrote in message
...
This is perfect! So, i'm newish to VBA, normally I would record a macro,
then
go from there. I'm sure there's an easier way to this clean function to an
entire row, that in the macro, i wouldn't have to use another row. i'd
imagine just a place holder variable.

can someone (PT?) help with this as well?

thanks so much,
-m

"Peter T" wrote:

Hi Mark,

in say A2
= Clean(A1)
copy across, then copy row 2 and paste special values

Regards,
Peter T




"mvyvoda" wrote in message
...
I have a sheet that in Row 1 has text. Within that text, there is also
a
character/s (maybe carriage return, not sure) that is messing
everything
up.

I need a way to automate (e.g. macro) that row only having "valid
characters." i'm not sure if i would use an ascii chart or what to
weed
them
out. i can only have characters 32 - 126, in an ascii chart. I can
send
the
sheet in question.

thanks,
mark

I think when setting a ref to .EntireRow or .EntireColumn, and going to
process each cell in the ref, need to include .Cells,

set rng = ActiveCell.EntireRow.Cells

alternatively
set rng = ActiveCell.EntireRow
For each cell in rng.Cells

Regards,
Peter T

"Tom Ogilvy" wrote in message
...
Dim rng as Range, cell as Range
set rng = ActiveCell.EntireRow
for each cell in rng
if not isempty(cell) then
cell = application.Clean(cell)
end if
Next

--
Regards,
Tom Ogilvy


"mvyvoda" wrote in message
...
This is perfect! So, i'm newish to VBA, normally I would record a macro,
then
go from there. I'm sure there's an easier way to this clean function to
an
entire row, that in the macro, i wouldn't have to use another row. i'd
imagine just a place holder variable.

can someone (PT?) help with this as well?

thanks so much,
-m

"Peter T" wrote:

Hi Mark,

in say A2
= Clean(A1)
copy across, then copy row 2 and paste special values

Regards,
Peter T




"mvyvoda" wrote in message
...
I have a sheet that in Row 1 has text. Within that text, there is
also
a
character/s (maybe carriage return, not sure) that is messing
everything
up.

I need a way to automate (e.g. macro) that row only having "valid
characters." i'm not sure if i would use an ascii chart or what to
weed
them
out. i can only have characters 32 - 126, in an ascii chart. I can
send
the
sheet in question.

thanks,
mark

Yes. My Bad. Thanks for the correction.

--
Regards,
Tom Ogilvy


"Peter T" <peter_t@discussions wrote in message
...
I think when setting a ref to .EntireRow or .EntireColumn, and going to
process each cell in the ref, need to include .Cells,

set rng = ActiveCell.EntireRow.Cells

alternatively
set rng = ActiveCell.EntireRow
For each cell in rng.Cells

Regards,
Peter T

"Tom Ogilvy" wrote in message
...
Dim rng as Range, cell as Range
set rng = ActiveCell.EntireRow
for each cell in rng
if not isempty(cell) then
cell = application.Clean(cell)
end if
Next

--
Regards,
Tom Ogilvy


"mvyvoda" wrote in message
...
This is perfect! So, i'm newish to VBA, normally I would record a
macro,
then
go from there. I'm sure there's an easier way to this clean function
to
an
entire row, that in the macro, i wouldn't have to use another row. i'd
imagine just a place holder variable.

can someone (PT?) help with this as well?

thanks so much,
-m

"Peter T" wrote:

Hi Mark,

in say A2
= Clean(A1)
copy across, then copy row 2 and paste special values

Regards,
Peter T




"mvyvoda" wrote in message
...
I have a sheet that in Row 1 has text. Within that text, there is
also
a
character/s (maybe carriage return, not sure) that is messing
everything
up.

I need a way to automate (e.g. macro) that row only having "valid
characters." i'm not sure if i would use an ascii chart or what to
weed
them
out. i can only have characters 32 - 126, in an ascii chart. I can
send
the
sheet in question.

thanks,
mark

awesome!! this worked. it seemed so easy.

thanks for the help AGAIN!!,
Mark

"Tom Ogilvy" wrote:

Dim rng as Range, cell as Range
set rng = ActiveCell.EntireRow
for each cell in rng
if not isempty(cell) then
cell = application.Clean(cell)
end if
Next

--
Regards,
Tom Ogilvy


"mvyvoda" wrote in message
...
This is perfect! So, i'm newish to VBA, normally I would record a macro,
then
go from there. I'm sure there's an easier way to this clean function to an
entire row, that in the macro, i wouldn't have to use another row. i'd
imagine just a place holder variable.

can someone (PT?) help with this as well?

thanks so much,
-m

"Peter T" wrote:

Hi Mark,

in say A2
= Clean(A1)
copy across, then copy row 2 and paste special values

Regards,
Peter T




"mvyvoda" wrote in message
...
I have a sheet that in Row 1 has text. Within that text, there is also
a
character/s (maybe carriage return, not sure) that is messing
everything
up.

I need a way to automate (e.g. macro) that row only having "valid
characters." i'm not sure if i would use an ascii chart or what to
weed
them
out. i can only have characters 32 - 126, in an ascii chart. I can
send
the
sheet in question.

thanks,
mark