Excel - how to create new column at empty cell

Question

I have been sent an Excel file of an export table of our contacts. However, all details have been exported into a single column:

Example

User 1
User 1 Title
Address
User 1 ID


User 2
User 2 Title
Address
User 2 ID

and so on for several thousand rows.

What I need is to convert it into a table.

Is it possible that at every empty cell to create a new column?

I have tried the answer here but I am getting run time error 6.

I do not know if it is because I have 4 or 5 empty cells in a row or whether I have done something else wrong.

How can I fix this?

Thanks.

Answer 1

Change the

Dim firstCellRow As Integer

to

Dim firstCellRow As Long

An integer can hold values from -32767 to to 32768 and your spreadsheet likely has over 32,768 rows.

The following should also be changed from Integer to Long:

Dim startIndex As Integer
Dim endIndex As Integer
Dim lastRow As Integer

...

Dim deleteFrom As Long
Dim deleteTo As Long