+ Reply to Thread
Results 1 to 10 of 10

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

  1. #1
    Registered User
    Join Date
    04-19-2020
    Location
    UK
    MS-Off Ver
    2010 x64
    Posts
    6

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

    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

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,008

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

    Quote Originally Posted by DonaldDukw View Post
    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.
    Last edited by AlphaFrog; 04-19-2020 at 09:03 AM. Reason: typo
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    04-19-2020
    Location
    UK
    MS-Off Ver
    2010 x64
    Posts
    6

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

    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

  4. #4
    Registered User
    Join Date
    04-19-2020
    Location
    UK
    MS-Off Ver
    2010 x64
    Posts
    6

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

    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
    Last edited by DonaldDukw; 04-19-2020 at 09:33 AM.

  5. #5
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,008

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

    Is this faster?

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    04-19-2020
    Location
    UK
    MS-Off Ver
    2010 x64
    Posts
    6

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

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

    It takes 5-6 seconds to run your code
    Last edited by DonaldDukw; 04-19-2020 at 09:44 AM.

  7. #7
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,008

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

    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.

  8. #8
    Registered User
    Join Date
    04-19-2020
    Location
    UK
    MS-Off Ver
    2010 x64
    Posts
    6

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

    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.

  9. #9
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,008

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

    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.

  10. #10
    Registered User
    Join Date
    04-19-2020
    Location
    UK
    MS-Off Ver
    2010 x64
    Posts
    6

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

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro to select a variable row range and copy it down a variable amount of times
    By JPoFresh in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-19-2019, 10:40 AM
  2. [SOLVED] Macro to select a variable cell range and copy it down a variable amount of times
    By JPoFresh in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 07-18-2019, 09:17 PM
  3. Copy Range of Formulas to a Variable Range of Cells
    By xace in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 09-07-2015, 10:28 PM
  4. Copy variable range from variable workbook to variable range on current workbook
    By Locster79 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-09-2013, 10:37 AM
  5. [SOLVED] Copy range from multiple sheets, into a master sheet and moving over by variable col range
    By g1eagle in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-23-2012, 01:36 PM
  6. [SOLVED] Copy/Paste Values from Variable Range to Corresponding Variable Range
    By Cpetersoc in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-14-2012, 08:20 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1