Hi all,

I've got the job of converting Excel sheets from customers into our system's tables.
The customer data could have come from anything, from an Excel spreadsheet(s) to FileMakerPro 'exported' to Excel or ???

I thought I'd tackle the toughest job first... converting text fields larger than 254 characters into multiple adjacent columns of 254 chunks so that they can be re-assembled as memo fields later.

After much trial and tribulation I've got some VBA code that does the job BUT the insertion of columns takes FOREVER. In my test worksheet with 6966 rows it takes ovr an HOUR to insert a SINGLE COLUMN.

I captured a macro of doing an INSERT Column, which happens nice and fast, and found that the generated code was a Range("DT:DT").Select then INSERT(Select) or something close to that.

The trouble is, I can't find the way to correctly identify the column that way for a RANGE.
I've been using the following construct:

For aa = 1 To Worksheets("Sheet1").Rows.Count
    Worksheets("Sheet1").Cells(aa, xx + 1).Insert (xlShiftToRight)
Next aa

and this takes an hour per insert!

I don't know how many columns or rows the spreadsheet to be processed will have, so I can't use fixed column IDs like "DT".

Can someone please tell me how to dynamically come up with the format required to allow me to use the SELECT/INSERT technique.
Strangely (at least to me) the property called "Cells" (used above) implies more than 1 by its name but I can't find a clear example of how I might do that code-wise.

Any help appreciated.