Using Array in VBA to eliminate blank columns in a row

**NitroN** · 12-03-2013, 10:42 AM

Hi All,
I have a table with the following:
..........A............B................C...............D..........E ........F...........G............H
1...... Name.....Address..........Tel........... Fruit1....Fruit2...Fruit3....Fruit4.......Fruit5
2...... Detail1......................Detail2.........Txt1................Txt2....................Txt3
3...... Detail1......................Detail2.........Txt1......Txt2....Txt3

(Dots are used as spacers to keep columns in line)

The columns for Name, address & Tel are allowed to have blank columns.
The columns for Fruit1 to Fruit5 need to be consolidated to the left, leaving no blank columns.
Row 2 is the original and row 3 is how it needs to look.

I can do this using an array formula in D3:H3
{=IFERROR(INDEX($D$3:$H$3, SMALL(IF(($D$3:$H$3="")+ISERROR(($D$3:$H$3), "", COLUMN(($D$3:$H$3)-MIN(COLUMN(($D$3:$H$3))+1), COLUMN(A1))),"")}

But I need to do this in VBA.
Can anyone assist?

**TMS** · 12-03-2013, 11:37 AM

One way, though I'm sure there are better:

Please Login or Register  to view this content.

Regards, TMS

**NitroN** · 12-09-2013, 08:04 AM

Hi Trevor,

I actually came up with a very similar answer, but I appreciate your input.

Regards
Neil

Please Login or Register  to view this content.

**TMS** · 12-09-2013, 08:37 AM

You're welcome. Thanks for the rep.

For what it's worth, my solution is quicker. But, for small amounts of data, it probably won't be that significant. Timing my routine is between 4 and 6 milliseconds; yours is between 23 and 25 milliseconds. Not a big deal when you're looking at milliseconds but, if you end up processing lots of data ...

See attached sample workbook.

HTML Code:

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.