0

I have a big sheet of data that changes after I run through some code. I was thinking about a way to bring all that data back to life before running the code. I want to do this without creating any new sheets - just store the values in an object/variable and return it back after running my code.

I've tried doing something like 

Dim myrng As Range

myrng = sheets(x).Usedrange  'storing the sheet contents

SheetS(x).Range("A1") = myrng 'bringing back original sheet contents to the same sheet

I know this might look stupid but I really have no idea if I should be doing some copy-paste application or whatsoever.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Juras Sulcas
  • 52
  • 7
  • `UsedRange` will only store the Address. Like in Real life, your Home Address, but not anything about who lives there. That can change, but address won't – Mikku Jul 23 '19 at 10:48
  • Yeah, that's what I kind of thought. So how can I store the contents? – Juras Sulcas Jul 23 '19 at 10:49
  • Simplest way would be to create a New Sheet and delete after operation is done. But as you don't want to do that, you can create an array and save all the values of the UsedRange in It. then you can loop and Print them Back. – Mikku Jul 23 '19 at 10:50
  • 1
    @JurasSulcas, What if you programmatically created a new sheet, copied the range, and then deleted it after pasting back (all programmatically?) – AAA Jul 23 '19 at 10:55
  • @JurasSulcas ..See the Answer. You can save the Contents in a Array and Print it back like that – Mikku Jul 23 '19 at 11:01
  • Try avoiding `UsedRange`. Please refer this answer: stackoverflow.com/a/50434743/6908282 – Gangula Jul 23 '19 at 11:12

1 Answers1

1

This will Work:

Dim rng As String
Dim dat() As Variant

rng = Sheets(x).UsedRange.Address
dat = Sheets(x).UsedRange.Value  'storing the sheet contents

'Do changes on Sheet

Sheets(x).Range(rng) = dat()

Explanation:

  • Saved the Address of UsedRange in a String
  • Saved the Contents of UsedRange in a Array
  • Pasted the Array Back in the Saved Address in 1st Step
Mikku
  • 6,538
  • 3
  • 15
  • 38
  • Try avoiding `UsedRange`. Please refer this answer: https://stackoverflow.com/a/50434743/6908282 – Gangula Jul 23 '19 at 11:12
  • 2
    @Gangula `UsedRange` is applicable in this situation. What you are referring to is getting an _absolute_ row/column from the `UsedRange` via `UsedRange.Rows.Count` or `UsedRange.Columns.Count`. Doesn't matter if Rows 1 through 500.000 are empty in this case, since `UsedRange.Address` will still give us the address of row 500.001. – Nacorid Jul 23 '19 at 11:43
  • Thank you @Nacorid, I didn't realize that. – Gangula Jul 23 '19 at 12:00