Text to Columns bug: offsetting by 1 row

mbt2103

New Member
Joined
Aug 4, 2014
Messages
35
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:
Code:
    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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,647
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 4, 2014
Messages
35
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,647
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 4, 2014
Messages
35
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,521
Messages
5,625,294
Members
416,087
Latest member
drth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top