VBA Array copying of formats

[jwk]

I am producing a condensed version of a report, reducing the number of columns from around 500 to around 100. I am doing this by capturing the entire range as an array, then passing only the columns I want to a new array and outputing back to worksheet. Very Fast for the data.

However, I also would like to bring in the column formatting --fonts, colors, etc. from the original columns. So I would like to essentially delete the columns I don't want, without explicitly deleting them -- this is way too slow.

Ideas for bringing in the formatting from the original column, or 're-attaching' the formatting back to the data?

I'm a big fan of array processing...

Thanks -- and looking forward to the collective brainpower being harnessed here!

 

[GTO]

Hi there,

Not tried, but I would think that trying to save the various formatting properties and re-apply them to the destination columns would be slow, no?

If you know which columns you are deleting, you can do so in a non-continguous fashion:

    Range("B:C,E:H,J:L,N:O").Columns.Delete

 

[jwk]

Thanks GTO -- I'm about to embark on looping through every cell in the range --hopefully via an array -- and capturing the formatting, then re-applying -- to see how fast it may be.

I've played with your array of columns method, but it has not worked well; I keep getting overlapping range errors, maybe due to the fact there are merged cells that make the column selections less exact.

 

[GTO]

Thanks GTO -- I'm about to embark on looping through every cell in the range --hopefully via an array -- and capturing the formatting, then re-applying -- to see how fast it may be.

I've played with your array of columns method, but it has not worked well; I keep getting overlapping range errors, maybe due to the fact there are merged cells that make the column selections less exact.

The bane of worksheets!

Just curious, wouldn't you be unmerging them at some time anyway?

 

[jwk]

Hmm... Maybe so. Might be worth it on a couple of fronts to de-merge.