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'.

Option Explicit

Const sTestSheetNAME = "Test Bed"
Const sOriginalDataSheetNAME = "Sheet1"

Const nFirstDataROW = 5

Sub ClearOneRowAtATime()
  'This clears all data from the first row (starting at row 5) that contains data

  Dim myWorkbook As Workbook
  Dim myWorksheet As Worksheet
  
  Dim iCount As Long
  Dim iLastRowUsed As Long
  Dim iRow As Long

  'Create the Worksheet Objects
  Set myWorkbook = ThisWorkbook
  Set myWorksheet = myWorkbook.Sheets(sTestSheetNAME)
  
  'Find the Last Row Used (NOTE: A runtime error will occur if the entire sheet has no data)
  iLastRowUsed = myWorksheet.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

  'Find the First Row with Data (counts Numbers only)
  'Clear Data From that Row
  For iRow = nFirstDataROW To iLastRowUsed
  
    iCount = Application.WorksheetFunction.Count(myWorksheet.Rows(iRow))
    'Debug.Print iRow, iCount 'Output to the Immediate Window (Ctrl G in the Debugger)
    
    'If the Row contains Numbers, clear all data from the Row and Exit the loop
    If iCount > 0 Then
      myWorksheet.Rows(iRow).ClearContents
      Exit For
    End If
  
  Next iRow

  'Clear Object Points
  Set myWorkbook = Nothing
  Set myWorksheet = Nothing
  
End Sub

Sub RestoreOneRowAtATime()
  'This restores data from the first row (starting at from the rear) that contains no data

  Dim myWorkbook As Workbook
  Dim mySourceWorksheet As Worksheet
  Dim myDestinationWorksheet As Worksheet
  
  Dim r As Range

  Dim iCount As Long
  Dim iLastRowUsed As Long
  Dim iRow As Long
  
  'Create the Worksheet Objects
  Set myWorkbook = ThisWorkbook
  Set mySourceWorksheet = myWorkbook.Sheets(sOriginalDataSheetNAME)
  Set myDestinationWorksheet = myWorkbook.Sheets(sTestSheetNAME)
  
  'Find the Last Row Used (NOTE: A runtime error will occur if the entire sheet has no data)
  iLastRowUsed = myDestinationWorksheet.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  
  'Find the First Row with Data (counts Numbers only)
  'Copy Data From the Source Worksheet Row to the Destination Worksheet Row, then Exit the Loop
  For iRow = nFirstDataROW To iLastRowUsed
  
    iCount = Application.WorksheetFunction.Count(myDestinationWorksheet.Rows(iRow))
    'Debug.Print iRow, iCount 'Output to the Immediate Window (Ctrl G in the Debugger)
    
    'If the Row contains Numbers, Copy the 'Previous Row' Numbers to the Destination Worksheet, then Exit the loop
    If iCount > 0 Then
      myDestinationWorksheet.Rows(iRow - 1).Value = mySourceWorksheet.Rows(iRow - 1).Value
      Exit For
    End If
  
  Next iRow
    
  'Clear Object Points
  Set myWorkbook = Nothing
  Set mySourceWorksheet = Nothing
  Set myDestinationWorksheet = Nothing

End Sub

Sub RestoreAllData()
  'This restores all Data from the 'Original' Sheet to the 'Test Bed' Sheet

  Dim myWorkbook As Workbook
  Dim mySourceWorksheet As Worksheet
  Dim myDestinationWorksheet As Worksheet
  
  Dim r As Range

  'Create the Worksheet Objects
  Set myWorkbook = ThisWorkbook
  Set mySourceWorksheet = myWorkbook.Sheets(sOriginalDataSheetNAME)
  Set myDestinationWorksheet = myWorkbook.Sheets(sTestSheetNAME)
  
  'Copy All Data From Source WorkSheet to the Destination Worksheet
  For Each r In mySourceWorksheet.UsedRange
    myDestinationWorksheet.Range(r.Address).Value = r.Value
  Next r
    
  'Clear Object Points
  Set myWorkbook = Nothing
  Set mySourceWorksheet = Nothing
  Set myDestinationWorksheet = Nothing

End Sub

Lewis

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 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 :

Option Explicit

Sub cpyPaste()
    
Dim ws As Worksheet
Dim cell As Variant
Dim LastRow As Long

Set ws = Sheets("Sheet2")
   
LastRow = ws.Cells(Rows.Count, 5).End(xlUp).Row
    
    Application.ScreenUpdating = False
    
    Sheets("Sheet1").Range("D5:L5").Copy
    ws.Activate
    ws.Cells(LastRow + 1, 4).Select
    ws.Paste
    Sheet1.Rows(5).Delete
    Sheets("Sheet1").Activate
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
End Sub

Sub renew()
Dim ws As Worksheet
Dim cell As Variant
Dim LastRow As Long

Set ws = Sheets("Sheet2")
   
LastRow = ws.Cells(Rows.Count, 5).End(xlUp).Row

Application.ScreenUpdating = False

If ws.Cells(LastRow, 5).Value = "" Then
    MsgBox "All data retrieved.", vbInformation, "Data Retrieval"
    Exit Sub
End If

    Application.ScreenUpdating = False
    ws.Cells(LastRow, 5).EntireRow.Copy
    Sheets("Sheet1").Rows(5).Insert
    ws.Cells(LastRow, 5).EntireRow.Delete
    Application.CutCopyMode = False
    
    ws.Activate
    ws.Range("A1").Select
    Sheet1.Activate
    
Application.ScreenUpdating = True

End Sub

Sub usdRng()
Dim LastRow As Long
Dim ws As Worksheet

Set ws = Sheets("Sheet2")

LastRow = ws.Cells(Rows.Count, 5).End(xlUp).Row

Application.ScreenUpdating = False

If ws.Cells(LastRow, 5).Value = "" Then
    MsgBox "All data retrieved.", vbInformation, "Data Retrieval"
    Exit Sub
End If

    Worksheets("Sheet2").Activate
    ActiveSheet.UsedRange.Copy

    Sheets("Sheet1").Select
    Sheets("Sheet1").Range("D5").Insert shift:=xlShiftDown
    
    Worksheets("Sheet2").Cells.Delete
    Application.CutCopyMode = False
    
    ws.Activate
    ws.Range("A1").Select
    Sheet1.Activate
    
Application.ScreenUpdating = True

End Sub

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.

Option Explicit

Sub cpyPaste()
    
Dim ws As Worksheet
Dim cell As Variant
Dim LastRow As Long

Set ws = Sheets("Sheet2")
   
LastRow = ws.Cells(Rows.Count, 5).End(xlUp).Row
    
    Application.ScreenUpdating = False
    
    Sheets("Input Page").Range("D2:L2").Copy
    ws.Activate
    ws.Cells(LastRow + 1, 4).Select
    ws.Paste
    Sheet1.Rows(2).Delete
    Sheets("Input Page").Activate
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
End Sub

Sub renew()
Dim ws As Worksheet
Dim cell As Variant
Dim LastRow As Long

Set ws = Sheets("Sheet2")
   
LastRow = ws.Cells(Rows.Count, 5).End(xlUp).Row

Application.ScreenUpdating = False

If ws.Cells(LastRow, 5).Value = "" Then
    MsgBox "All data retrieved.", vbInformation, "Data Retrieval"
    Exit Sub
End If

    Application.ScreenUpdating = False
    ws.Cells(LastRow, 5).EntireRow.Copy
    Sheets("Input Page").Rows(2).Insert
    ws.Cells(LastRow, 5).EntireRow.Delete
    Application.CutCopyMode = False
    
    ws.Activate
    ws.Range("A1").Select
    Sheet1.Activate
    
Application.ScreenUpdating = True

End Sub

Sub usdRng()
Dim LastRow As Long
Dim ws As Worksheet

Set ws = Sheets("Sheet2")

LastRow = ws.Cells(Rows.Count, 5).End(xlUp).Row

Application.ScreenUpdating = False

If ws.Cells(LastRow, 5).Value = "" Then
    MsgBox "All data retrieved.", vbInformation, "Data Retrieval"
    Exit Sub
End If

    Worksheets("Sheet2").Activate
    ActiveSheet.UsedRange.Copy

    Sheets("Input Page").Select
    Sheets("Input Page").Range("D2").Insert shift:=xlShiftDown
    
    Worksheets("Sheet2").Cells.Delete
    Application.CutCopyMode = False
    
    ws.Activate
    ws.Range("A1").Select
    Sheet1.Activate
    
Application.ScreenUpdating = True

End Sub

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?