texttocolumns

D

Doug Broad

A spreadsheet sent by email contains empty strings in
blank cells (I think) that stop text in cells to the left of
them from flowing across the blank cells to the right.

I have found that using texttocolumns eliminates whatever
data exists in the apparently blank cells allowing the
text to flow better.

For each column in the usedrange, I want to run the
texttocolumn operation using a fixed width such that
each column of the spreadsheet parses to only one
column of text. I have tried the following which works
most of the time but not all of the time. What could
I be doing wrong?

With ActiveSheet
For Each col In .UsedRange.Columns
col.TextToColumns DataType:=xlFixedWidth, fieldinfo:=Array(Array(0, 1), Array(80, 9))
Next col

Thanks
 
B

Bill Renaud

It looks like your code is not parsing anything at all, but merely taking
the first 80 characters of each column and truncating everything after that
in each cell. Do you really just want to widen out the columns so that you
can see all of the text in each column? If so, then you might try:

ActiveSheet.UsedRange.Columns.AutoFit
 
D

Doug Broad

Bill,
Your right. I don't want to parse. I want the side effect
that texttocolumns causes. TextToColumns clears the cells
that look blank but aren't. Until today, the code seemed
to work fine. Today, I got messages of "No object
to parse". The code triggered a question of "Do you
want to replace the destination cells?"

I ended up commenting that portion of code out and
doing the operation manually, column by column.

Thanks for your response.

Regards,
Doug
 
B

Bill Renaud

I was able to roughly duplicate your problem by putting something in column
$B when starting the macro, but then clearing it before the macro got to
that column. Excel will then throw a 1004 trappable error because the column
is actually empty. Somehow, the UsedRange must be "seeing" some data or
formatting in the column that actually has nothing for the TextToColumns
method to parse, hence the error.

You might have to put an error handler in the routine at the top just to
cover this situation:
On Error Resume Next
--
Regards,
Bill


Doug Broad said:
Bill,
Your right. I don't want to parse. I want the side effect
that texttocolumns causes. TextToColumns clears the cells
that look blank but aren't. Until today, the code seemed
to work fine. Today, I got messages of "No object
to parse". The code triggered a question of "Do you
want to replace the destination cells?"

I ended up commenting that portion of code out and
doing the operation manually, column by column.

Thanks for your response.

Regards,
Doug
Click to expand...
 
D

Dave Peterson

You could also check to see if there were data to parse:

Option Explicit
Sub testme01()

Dim Col As Range

With ActiveSheet
For Each Col In .UsedRange.Columns
If Application.CountA(Col) > 0 Then
Col.TextToColumns DataType:=xlFixedWidth, _
fieldinfo:=Array(Array(0, 1), Array(80, 9))
End If
Next Col
End With

End Sub

But I'm not sure how you got the overwrite prompt. You're keeping 80 columns
and skipping the rest.
 
Top