HELP PLS: On Paste - Fire program to delete data below new records pasted

shilabrow · #1 07-10-2014, 09:20 AM

I need help in deleting rows below what is pasted in a worksheet. If I paste data in A1 to Z500. I am wondering if upon paste, there can be a code to detect my new pasted data entries and delete any rows below new pasted data, if no rows below what is pasted then it should ignore. Is this possible? I will really appreciate all the help in getting this done. Thanks much.

excelledsoftware · #2 07-10-2014, 09:06 PM

Here is some code that should do it. You will need to change the FinalRow. Please be careful when running this code because it will delete all the data below whatever you paste. Have a backup and then try it. Also this procedure is to be used when you are about to paste your data. Meaning it pastes the data for you. Just select the cell where you want to paste and then run the code. You may want to attach the code to an autoshape on your worksheet.

Code:

Sub DeleteAfterPaste()

'IMPORTANT this code will DELETE data from a worksheet.  Be sure to only have the affected workbook open
'when running.


  Dim c As Variant
  Dim LastPasteRow As Long, FinalRow As Long, DataRows As Long
  
    FinalRow = 3000 'Change this to the final row of data.
  
    On Error GoTo Clipboardempty
  
    ActiveSheet.Paste
    On Error GoTo 0 'return errors back to normal.
    
    'Run a loop to find the last row
    For Each c In Selection
      LastPasteRow = c.Row
    Next c
    
    DataRows = WorksheetFunction.CountA(Range(LastPasteRow + 1 & ":" & FinalRow))
    If DataRows = 0 Then
      End 'Ends the program because nothing is below
    Else
      'This is the command that deletes everything below.
      Range(LastPasteRow + 1 & ":" & FinalRow).ClearContents
    End If
    
    End
Clipboardempty:
  'Checks if the clipboard is empty and if so halts execution
  MsgBox "Nothing to paste, program ending."
  End
  
End Sub

Let me know if you have any questions.

Thanks

shilabrow · #3 07-12-2014, 11:31 AM

Thanks excelledsoftware, it works great. I really appreciate this.

I was wondering if I could put a restriction on the code paste to start at cell A1 or prompt a message that the A1 cell must be selected to paste the code. This is to ensure that the paste starts at cell A1. Thanks much.

excelledsoftware · #4 07-12-2014, 12:09 PM

Hi Shila this can be easily done by adding the following right above the line that says ActiveSheet.paste

Code:

Range("A1").select

If you prefer the msgbox that can be done as well but probably isn't necessary.

shilabrow · #5 07-12-2014, 03:16 PM

Thanks excelledsoftware, I appreciate your help. Really do! My last questions is just for me to understand a portion of the code which is "FinalRow = 3000 'Change this to the final row of data.

I changed to extend the range but was wondering if I necessarily have to change the data to the last row of data because my data grows at times so I just put 30000 - which works but is there any implications later?

"FinalRow = 30000 'so as to accomodate much range in future - is this right?

Thanks and wouldn't need the msgbox since no need for it.

excelledsoftware · #6 07-12-2014, 04:00 PM

Shila, First off you are very welcome. It helps me learn coding better to help when I can. The finalrow is where the data will never go past. The reason I set this up is because in Excel 2003 (I have not tested it in newer version) when you delete huge sets of data it has a tendency to move the file size up. I had one file that went from 23kb all the way to 23 mbs! This may be just with formatting but I wanted to make sure. You could leave it at 30,000 and watch if the file size goes way up. If it dosnt then kewl. But if it does you have the ability to control it with this variable.

shilabrow · #7 07-13-2014, 04:17 PM

Thanks excelledsoftware, got it - much appreciated.

shilabrow · #8 07-14-2014, 08:00 AM

Hi excelledsoftware, I need help again.. I tried combining the code to my exisiting code to make one code to run for my final output but it wont combine. the code just stops at where your paste code ends. I have it below. What can I do to continue execution of the code to generate my final output. I copied sample below -

Sub Report()
'IMPORTANT this code will DELETE data from a worksheet.
Dim c As Variant
Dim LastPasteRow As Long, FinalRow As Long, DataRows As Long

FinalRow = 30000 'Change this to the choice of final row of data.

On Error GoTo Clipboardempty
Range("A1").Select
ActiveSheet.Paste
On Error GoTo 0 'return errors back to normal.

'Run a loop to find the last row

For Each c In Selection
LastPasteRow = c.Row
Next c

DataRows = WorksheetFunction.CountA(Range(LastPasteRow + 1 & ":" & FinalRow))
If DataRows = 0 Then
End 'Ends the program because nothing is below
Else
'This is the command that deletes everything below.
Range(LastPasteRow + 1 & ":" & FinalRow).ClearContents
End If

End
Clipboardempty:
'Checks if the clipboard is empty and if so halts execution
MsgBox "Nothing to paste, program ending."
End
'NEXT clean data to generate report
'To remove the * from the file
Sheets("DATA").Select
Range("QueryData").Select
Columns("F:F").Select
Selection.Replace What:="~*", Replacement:="", LookAt _
:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False

'To Custom Sort the Subcategories in the right order needed in the Reports
ActiveWorkbook.Worksheets("DATA").Sort.SortFields. Clear
ActiveWorkbook.Worksheets("DATA").Sort.SortFields. Add Key:=Range("F:F"), _
SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _ ' there is more but I just cut off - runs alone but when I combine the codes ends at paste, help please. Thanks

End With

'This refreshes all Pivot Table Element for Report
ActiveWorkbook.RefreshAll

'This is to autofit the sheet
Set mysheet = ActiveSheet
For Each Sheet In Worksheets
Sheet.Select
Cells.EntireColumn.AutoFit
Next Sheet
mysheet.Select
'To select the first cell
Sheets("Sort").Select

excelledsoftware · #9 07-14-2014, 05:42 PM

Hi Shila, based off the line that says there is more I am not able to test it but I think all you need to do is move a couple lines of code to make it work. After the the code I wrote runs it has a line that says END.
However we can't just remove the END to make it work. The END was there because in the event that your clipboard was empty the code need to go to the clipboardempty error. This is called an error handler. If the code runs without the need of the error handler then the command END is necessary to keep the code from running the handler at the very end. To fix it do the following:

I have a comment that says the following 'This is the command that deletes everything below.
You need to remove the END after the END IF that is after this line. Now once that is done it will continue to the code you pasted but there is one more thing to fix. The Error handler clipboardempty needs to go at the bottom of the code right before END SUB. you need to move all lines from clipboardempty: to where it says END. This is so the error handler is not gone but we dont want it to run right here. And that brings us to the final step. In the event that you do not need the error handler we need the code to end. You would write END right before the error handler. I understand this may be confusing but I wanted you to understand the why behind it. I have pasted the modified code below. Again I have not tested it but it seems like that should fix it.

Code:

Sub Report()
'IMPORTANT this code will DELETE data from a worksheet.
Dim c As Variant
Dim LastPasteRow As Long, FinalRow As Long, DataRows As Long

FinalRow = 30000 'Change this to the choice of final row of data.

On Error GoTo Clipboardempty
Range("A1").Select
ActiveSheet.Paste
On Error GoTo 0 'return errors back to normal.

'Run a loop to find the last row

For Each c In Selection
LastPasteRow = c.Row
Next c

DataRows = WorksheetFunction.CountA(Range(LastPasteRow + 1 & ":" & FinalRow))
If DataRows = 0 Then
End 'Ends the program because nothing is below
Else
'This is the command that deletes everything below.
Range(LastPasteRow + 1 & ":" & FinalRow).ClearContents
End If

'NEXT clean data to generate report
'To remove the * from the file
Sheets("DATA").Select
Range("QueryData").Select
Columns("F:F").Select
Selection.Replace What:="~*", Replacement:="", LookAt _
:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False

'To Custom Sort the Subcategories in the right order needed in the Reports
ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Add Key:=Range("F:F"), _
SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:=1
' there is more but I just cut off - runs alone but when I combine the codes ends at paste, help please. Thanks

End With

'This refreshes all Pivot Table Element for Report
ActiveWorkbook.RefreshAll

'This is to autofit the sheet
Set mysheet = ActiveSheet
For Each Sheet In Worksheets
Sheet.Select
Cells.EntireColumn.AutoFit
Next Sheet
mysheet.Select
'To select the first cell
Sheets("Sort").Select
End

Clipboardempty:
'Checks if the clipboard is empty and if so halts execution
MsgBox "Nothing to paste, program ending."
End
End Sub

Let me know if that works.

Thanks

shilabrow · #10 07-15-2014, 06:35 AM

Thanks excelledsoftware - I tried moving the code around as you suggested but not working - Below is a full revised version of line of code that you should be able to test with. I greatly appreciate your help. Thanks

Sub Report()
'IMPORTANT this code will DELETE data from a worksheet.
Dim c As Variant
Dim LastPasteRow As Long, FinalRow As Long, DataRows As Long

FinalRow = 30000 'Change this to the choice of final row of data.

On Error GoTo Clipboardempty
Range("A1").Select
ActiveSheet.Paste
On Error GoTo 0 'return errors back to normal.

'Run a loop to find the last row

For Each c In Selection
LastPasteRow = c.Row
Next c

DataRows = WorksheetFunction.CountA(Range(LastPasteRow + 1 & ":" & FinalRow))
If DataRows = 0 Then
End 'Ends the program because nothing is below
Else
'This is the command that deletes everything below.
Range(LastPasteRow + 1 & ":" & FinalRow).ClearContents
End If

'NEXT clean data to generate report
'To remove the * from the file
Sheets("DATA").Select
Range("QueryData").Select
Columns("F:F").Select
Selection.Replace What:="~*", Replacement:="", LookAt _
:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False

'To Custom Sort the Subcategories in the right order needed in the Reports
ActiveWorkbook.Worksheets("DATA").Sort.SortFields. Clear
ActiveWorkbook.Worksheets("DATA").Sort.SortFields. Add Key:=Range("F:F"), _
SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"Banana,Apple,Tomatoes,Spices" _
, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("DATA").Sort
.SetRange Range("QueryData")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

'This refreshes all Pivot Table Element for Report
ActiveWorkbook.RefreshAll

'This is to autofit the sheet
Set mysheet = ActiveSheet
For Each Sheet In Worksheets
Sheet.Select
Cells.EntireColumn.AutoFit
Next Sheet
mysheet.Select
'To select the first cell
Sheets("Sort").Select

End

Clipboardempty:
'Checks if the clipboard is empty and if so halts execution
MsgBox "Nothing to paste, program ending."
End
End Sub

excelledsoftware · #11 07-15-2014, 04:59 PM

Hi Shila, Im working on testing this right now but I am running into the problem of not being able to see the result. Many parts of your code refer to named ranges and headers. Do have a sample workbook that you could post so I can properly test this. Sorry for all of the back and forth. Thanks for your patience so far.

shilabrow · #12 07-15-2014, 07:33 PM

Hi excelledsoftware, find attached a test Workbook. The cleaning portion of the code is not working with the paste delete code but they work individually. If you run just the Clean macro, that runs fine. See attached. Appreciate much.

Please note the Querydata is the table range. I put note in the code area. Thanks

excelledsoftware · #13 07-15-2014, 09:26 PM

OK I think my version of Excel is too old to process your sort commands or you have some other formatting that isnt on this example. In any case I changed the code a little bit and made some things a little more efficient since we are always starting at row 1. copy and paste this code into your main workbook, save a backup and try it. if it halts again make note of the error that comes up and let me know what it says. If the sorting part is not the issue then this should work.

Code:

Sub PasteDeleteandcleandata()


'Please note the QueryData is the range of data.
'I used the offset formula in the formulas to define name and bind the data should it grow in size or reduce.
'You can change it to the table range of the Data sheet which is A1:L30 in this test code.
'When I run the code without the paste delete code - it works fine but with the paste delete, it halts. Appreciate the help.
  
  'IMPORTANT this code will DELETE data from a worksheet.  Be sure to only have the affected workbook open
'when running.
  Dim c As Variant
  Dim LastPasteRow As Long, FinalRow As Long, DataRows As Long
  
    FinalRow = 3000 'Change this to the final row of data.
  
    On Error GoTo Clipboardempty
  Range("A1").Select
    ActiveSheet.Paste
    On Error GoTo 0 'return errors back to normal.
    
    'Run a loop to find the last row
  
      LastPasteRow = Selection.Rows.Count 'was able to remove the loop since
                                                          'we always start on row 1
      
    
    DataRows = WorksheetFunction.CountA(Range(LastPasteRow + 1 & ":" & FinalRow))
    If DataRows = 0 Then
      End 'Ends the program because nothing is below
    Else
      'This is the command that deletes everything below.
      Range(LastPasteRow + 1 & ":" & FinalRow).ClearContents
    End If
    

  
'To remove the * from the file
Sheets("DATA").Select
Range("QueryData").Select  'Please note the QueryData is the range of data - I used the offset formula in the formulas for the named manager.
Columns("F:F").Select
Selection.Replace What:="~*", Replacement:="", LookAt _
:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False

'To Custom Sort the Subcategories in the right order needed in the Reports
ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Add Key:=Range("F:F"), _
SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"Banana,Apple,Tomatoes,Spices" _
, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("DATA").Sort
.SetRange Columns("F:F")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

'This refreshes all Pivot Table Element for Report
ActiveWorkbook.RefreshAll

'This is to autofit the sheet
Set mysheet = ActiveSheet
For Each Sheet In Worksheets
Sheet.Select
Cells.EntireColumn.AutoFit
Next Sheet
mysheet.Select
'To select the first cell
Sheets("Sort").Select

    Exit Sub
Clipboardempty:
  'Checks if the clipboard is empty and if so halts execution
  MsgBox "Nothing to paste, program ending."
  End

End Sub

shilabrow · #14 07-15-2014, 10:55 PM

Hi excelledsoftware, it pastes and doesn't continue with the cleaning part and I do not get any error. It just paste and stops. What could be the problem, funny thing is they work individually. I appreciate your help with this. Thanks much

shilabrow · #15 07-16-2014, 09:34 AM

Hi excelledsoftware, I notice something. When I paste exact amount of data range onto the Data sheet. It doesnt continue with other procedures because of;
The line FinalRow = 30000 'change to final row of data
- I think the line is saying sure you still fine within the 30000 range and since I have nothing to delete anymore am not processing other code.

When I change the line FinalRow = 29. It does paste and continue processing the other code. The only reason I dont like this is because its limits my paste option to 29 rows even if my clipboard data is larger than 29. I wouldnt want to be changing the FinalRow = ? everytime I want to run the code. I prefer a default no for varied options.

But If the range of row copied and pasted is less than the data in Data sheet, lets say the range of data copied is 20 rows and FinalRow = 29 the deletepastefunction works and continues to process the other clean code.

Is there a way to work around this to make them sync together. Thanks much for your help so far - I appreciate this greatly.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Fire drop down Macro on change	carlosriver24	Word	1	08-02-2012 07:18 PM
Conditional Formatting over copied and pasted data	kb3264	Excel	6	07-11-2012 07:12 AM
Stop underlining pasted data	LarryStroup	PowerPoint	5	01-11-2012 12:47 PM
why when we copy and paste the "text box" then the pasted doesn't go to where the cur	Jamal NUMAN	Word	1	04-11-2011 03:54 AM
Ungrouping an object pasted from another program	bracker	PowerPoint	0	01-28-2011 11:11 AM