[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VBA excel, Copy Row to Memory for Later UnDo

Posted on 2011-09-10
5
Medium Priority
?
352 Views
Last Modified: 2012-05-12
I have a form with 3 CommandButtons.  
1. "Copy Row"  Button
2. "Clear Values" Button
3. "UnDo Row" Button

Here are the Click Actions for each Button:

Private Sub Copy_Row_Click()

      ' ....???? Selection.EntireRow ....Copy to Memory ???

End Sub


Private Sub Clear_Values_Click()

          Selection.EntireRow.Clear

End Sub


Private Sub Undo_Row_Click()

  ' ....???? Selection.EntireRow ......bring back the old Row....???..

End Sub

Open in new window



I am trying to find a way so that, when I hit the Button "Copy_Row" and Then Button "Clear Values"  to be able (if I decide at a later stage) to bring the entire cleared Row (with its Values and its Formulas) back to place.....

I am not looking for the standard method of copying the whole file because it is very big and it will slow the procedures down....

Thanks in advance,
Any ideas?

0
Comment
Question by:New_Alex
  • 3
  • 2
5 Comments
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 2000 total points
ID: 36516977
If you only ever wanted to be able to recover the most recently deleted row, you could store its values in a variant array, the restore them later.  But this would only restore values/formulas, not formatting:

Private mvSavedRow

Private Sub Copy_Row_Click()

      ' ....???? Selection.EntireRow ....Copy to Memory ???
      mvSavedRow = Application.Intersect(Selection.EntireRow, ActiveSheet.UsedRange).Formula

End Sub


Private Sub Clear_Values_Click()

          Selection.EntireRow.Clear

End Sub


Private Sub Undo_Row_Click()

  ' ....???? Selection.EntireRow ......bring back the old Row....???..
  Dim rng As Excel.Range
  
  Set rng = Selection.Cells(1)
  rng.Resize(1, UBound(mvSavedRow, 2)).Formula = mvSavedRow

End Sub

Open in new window

0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36516979
If you wanted formats and everything, you would probably have to copy the row to another (hidden?) sheet, then copy it back again
0
 
LVL 1

Author Comment

by:New_Alex
ID: 36517098
Hi andrew.

Is it possible to save the Row in a closed Workbook file and then retrieve it while workbook remains closed?

Thank you... Presentation is important for me!
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36517192
You couldn't save it in a closed workbook - it would have to be open in Excel to write to it and read from it. The first suggestion I made would not show anything to the user. If formatting is important you could possibly copy it from another row.
0
 
LVL 1

Author Closing Comment

by:New_Alex
ID: 36540789
Yes very good solution but limited. There are probably not a lot of things you can do generally for this
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question