Copy a range to a variable and restore the range from the variable

[DonaldDukw]

Hi, I hope you can help with what first appeared to be a simple problem but now has me tearing my hair out.


I have a worksheet with 'columns' of data beginning in row 7 (Row 7 is the header row for the columns).
Rows 1 to 6 has different data.

There can be up to 10,000 values in each 'column' and I need to change format and alignment.
Selecting the excel sheet columns and changing the alignment is fast but is slow if I use a range to select row 8 to last row.

Cells A1:K7 (range can change) has different formats and alignments to the 'columns' in row 8 so must be restored.

The idea is to copy cells A1:K7 (for example), format/align the full columns and restore cells A1:K7

I need cell formats and alignment but may need values and fonts too.



I thought of something like this

Dim MyRange as Range
Dim ws as worksheet


Set MyRange = ReturnRange("A1:A7")

' here I will align the columns in the range for speed (aligning range is slow)
' and want to restore the header cells alignment and formats


call RestoreRange("A1", MyRange) ' restore at top left cell. Range need not be same as when copied.



Any help really appreciated

Thanks

[AlphaFrog]

Selecting the excel sheet columns and changing the alignment is fast but is slow if I use a range to select row 8 to last row.

This doesn't sound correct. Can you show your "slow" code that does the partial column alignments? Please take note of my signature block below about the use of CODE tags.

If we can speed up your partial column alignments, restoring the top 7 rows is moot.

[DonaldDukw]

I think I can simplify my request as my question is more than a bit long winded........

a). How can I store a worksheet range to a variable ( cell format, alignments and maybe fonts)
b). How can I restore the contents from the saved variable.

Thanks

[DonaldDukw]

Fast Code ( no delay) :

Please Login or Register  to view this content.

Slow Code: ( 5+ seconds)

Please Login or Register  to view this content.

Thanks

[AlphaFrog]

Is this faster?

Please Login or Register  to view this content.

[DonaldDukw]

no I have everything turned off
It is just a range issue - always slow

It takes 5-6 seconds to run your code

[AlphaFrog]

Well done with the CODE tags.

That's weird.

To answer your question about storing formats in a variable, it's not quite as straightforward. You could assign a Range to a variable, but that doesn't lock in the format settings for that range-variable.

You can store individual format settings each to a variable, but if cells have different formats, you would need a variable for each cell that is different.

Alternatively, you could copy range A1:F7 to a temp location (say 100 columns to the right), then make your column changes, then restore the original A1:F7 from the temp copy. I don't know if that would be faster for you.

Please Login or Register  to view this content.

[DonaldDukw]

Thanks - got a merge cell error. We have a merged cell in the copy cells range.
/
Unfortunately this is a customers worksheet - I don't know it is safe to write to.

If there a way to paste the variable as a range?

Please Login or Register  to view this content.

[AlphaFrog]

Please Login or Register  to view this content.

The variable R will not retain the original format of A1:A7 if you change the format in A1:A7 as I tried to explain previously.

[DonaldDukw]

Ok I'll see if cells A1:?7 can be redrawn after columns are aligned.
Thanks for trying.