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