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 = 30000 'You should just be able to leave this
  
    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
      
    
    DataRows = WorksheetFunction.CountA(Range(LastPasteRow + 1 & ":" & FinalRow))
    If DataRows <> 0 Then
      'This is the command that deletes everything below.
      Range(LastPasteRow + 1 & ":" & FinalRow).ClearContents
    End If
  'removed condition that could end the program.
    

  
'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