Text to Columns bug: offsetting by 1 row

[mbt2103]

Hi all,

I'm using Excel 2010/VBA to create a rather lengthy, involved macro with the purpose of transforming 500+ formatted, pdf reports into individual lines of data. I'm currently hunting down minor bugs with one or two reports that come out differently than they should.

Early on in the code, I run through a couple instances of TextToColumns. These have worked flawlessly for the past few weeks.

This morning, without changing any code related to the TextToColumns functions, one of the two usages started malfunctioning. The code is as follows:

    Selection.TextToColumns Destination:=Selection, DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="{", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    Selection.Offset(0, 4).Select
    Selection.TextToColumns Destination:=Selection, DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

The first text to columns works fine. The second text to columns offsets the data by 1 row, which throws off my everything--especially if it's going to be happening essentially at random.

It didn't do this earlier this morning, but now it's doing it consistently. The only code I changed was later on in the macro, and these are my only two instance of TextToColumns, so defaults shouldn't have changed.

I searched, and I can't find any other instances of someone experiencing this bug--so I've concluded it must be user error. Help! I don't even know what I could have done to cause this ...

-Matt

 

[Joe4]

I am guessing that it might be a data issue. You might have something in your data that is being interpretted as an extra delimiter for certain lines.

I would focus on the rows that are not working right, and take a look at the underlying data before the macro is run. Is there anything odd looking about it? How is that line different from the other lines?

 

[mbt2103]

It's every row. All 40,000 lines offset by one row, as if I'm copying then pasting the entire selection one row above. The problem with it being a data issue is I ran it on the same test data 3 hours ago, and it didn't offset.

 

[Joe4]

Sounds like you may have a blank row at the very top of your data. If you do, and you are including it in your Selection, it will move all the rows up one row.

 

[mbt2103]

Hm. That fixed it. Thank you!

Ahhh ... and it seemed like it was only doing it for the second one because there were actually two blank lines at the top--so the first TextToColumns shifted everything up one, and the second TextToColumns shifted just that section up one, offsetting it from the rest of the data.