VBA code for 2 buttons that can clear cells in a row then second button to bring data back

A few questions on this...
1) When you click Clear the first time will it remove the first column of data, second time the second one etc.?
2) Clicking Retrieve should bring them back in the reverse order of how they were removed?
3) Do they need to be removed or could they just be hidden?

Beth.

I think it would be helpful to see this in context. It may be that it's easier to hide the row and amend the formulas than to get rid of a row and move the remaining data just to keep the formulas working. Would you be able to attach a copy of the full file? If there is sensitive information included then desensitize it first.

The reason I'm leaning toward hiding the data is to retrieve it, either at the click of the button or when closing the file, means you have to store it somewhere rather than just deleting it.

Beth.

I took a slightly different approach to your problem. In cases like yours, I usually use a 'Shadow Sheet' containing a copy of the original data and do all the testing on the 'Shadow' Sheet. When testing is complete, I delete the Shadow Sheet and use the Original Sheet in production. See the code that follows which is included in the Sample Workbook. All activity takes place on Sheet 'Test Bed'.

Please Login or Register  to view this content.

Lewis

Thanks BanginMyHeadOnMyDesk, I do have data that i can't desensitize and the file size of my workbook is way to large to attempt uploading anyway....The thing i worry about with "hiding" the data in rows is the other sheets have alot of cells referencing the ones we might apply a "hide" function to but if the data is still inside the cells of row 5 wouldn't the cells on other sheets still see them and continue referencing them? Make sense?

.
I believe this is what you were seeking :

Please Login or Register  to view this content.

Hillster wrote: < when I pasted your code into my master Workbook, then added the form control buttons, click them. it keeps opening your copy of the example work sheet>

The macros associated with the Control Buttons are still pointing to my file - a powerful feature of Excel when you want that feature.

Since you want the macros to point to your file, you have to reassign the buttons to point to the Macros of the same name in your file:
a. Right Click the Command Button
b. Select 'Assign Macro' and select the appropriate Macro, then select 'OK'.

Lewis

Thanks Logit, that is exactly what I want but the code is not working when I pasted it into a module in my main workbook. I do not have any sheets named "sheet1" or "sheet2" which i see in your code so I switched to the actual name of my sheet that needs the vba function, ("Input Page"). As expected it returned all kinds of Debug errors which I tried to resolve. No success.
I noticed part of the code appears to reference sheet2? is see sheet2 of your example has no data in it... why would vba be looking at a sheet with no data?

You can edit the code to match your sheet names. If you are having difficulty getting that to function properly ... let me know what your sheet names are and I can edit it for you. If I correctly understand, the main sheet with your data is called "input Page". Is that correct ?

The reason the code is looking for data in Sheet2 is because when you remove a line of data from your Input Page ... it must be placed somewhere so it can be retrieved later to be placed back on Input Page. The sheet for temporary placement of the data can be any sheet. If you are presently using Sheet2 for something else ... the project can utilize a different sheet .... it can be named "Temp Data" or anything else .... and perhaps even hide it to prevent accidentally using the sheet for something else.

Let me know.

Logit. you are correct, the sheet i am trying to control the data by using VBA is named "Input Page". I have created a "sheet2" sheet so your code can utilize the temporary data... One item i did discover is the data across the "Input Page" / sheet is getting scrambled up pretty badly when the vba runs... I wonder if we could try instead of removing the entire row 5, can vba remove only cells B5:G5, everything else in the vba code remains the same as far as creating the "Temp Sheet", the use of the clear and retrieve buttons etc... is that possible? and if you don't mind changing the "sheet1" reference to "Input Page", that would be awesome!!! Let me know your thoughts.

.
The code has been edited to what I believe you are seeking.

Please Login or Register  to view this content.

Workbook attached.

I think it's still removing the entire row 5 data "Sheets("Input Page").Range("D2:L2").Copy" instead i think it should be referencing "C5:F5" that is the actual row my data is on my workbook. If we could get that to work it would be great. The next trick will be me trying to actually get that code into my workbook and have it actually work properly. I don't even know where I am supposed to put the code when it gets to my workbook... I see where it is on your Example workbook...I'm sure it will be different on mine. I sure appreciate your help with this! Thank you!

.
Any chance you could share your workbook ? It would certainly cut down on all the back and forth.

If you need to you can send it to my private email. Let me know and I'll send you a private message.

Hey Logit, i sent you a follow up email. Did you receive it?