Cannot Clear Out Used Range

[bwink88]

The excel document attached to this thread has a macro designed to create a text string by taking data from the input tab, generating the text string on the TDIN Formula tab, and then copying the result on the TDIN Formula tab on the TDIN tab for each fund. The resulting text string on the TDIN tab is then used to import input data into tax software.

The macro in question is linked to the box at the top of the input tab ("Step 3: Create CCH Import File"). Before you run it, look at the tab titled TDIN, and notice that from row 288 to row 8867, all of the cells are blank, and after this break the text string picks back up and goes to row 11727. If you run the macro, it was designed to delete out all values in the TDIN tab and then paste the updated text string, but it keeps adding a big gap between the first fund and the rest of the funds, and the gap grows every time you run the macro. Every time, it will post the first fund in the place it is supposed to, but then it adds the second fund on after the original usedrange. I have the line "ActiveSheet.UsedRange" in the vba code, which up til 2 weeks ago, worked to clear the used range. Any idea how I can correct this?

Sub CreateTextString()

'Don't show Macro Process/Speed up macro
Applicationscreenupdating = False

'Clean TDIN sheet
Sheets("TDIN").Visible = True
Sheets("TDIN").Select
    ActiveSheet.Unprotect
    Range("A7").Select
    ActiveCell.FormulaR1C1 = "1"
    Selection.EntireRow.Delete
    Range("A2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Delete
    
Range("A2").Select

'Below Code Clears the Former Used Range

ActiveSheet.UsedRange


'Loop returns and paste formulas from TDIN Formulas to TDIN Sheet

Sheets("Input").Select
ActiveSheet.Unprotect
Range("A3").Select
ActiveCell.FormulaR1C1 = "1"

Sheets("Input").Select
For Each x In Range("CopyCount")
If Trim(x) <> "" Then
Range("CurrentFund") = x
Sheets("TDIN").Select
Sheets("TDIN Formulas").Visible = True
Sheets("TDIN Formulas").Select
 Application.Goto Reference:="TDINCopy1"
    Selection.Copy
    Sheets("TDIN").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.SpecialCells(xlLastCell).Select
    ActiveCell.Offset(1, 0).Select
Sheets("Input").Select
Range("CurrentFund").Select
End If
Next

'Select data for TDIN text file
Sheets("Input").Select
Range("A3").Select
ActiveCell.FormulaR1C1 = "1"

Sheets("TDIN").Select
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingColumns:=True


Sheets("Input").Select
Range("B7").Select
ActiveSheet.Protect

'Allow macro process to show while running
Applicationscreenupdating = True

MsgBox "Your File Has Been Created and Saved as " & Range("SavePath") & "\" & Range("SaveName"), vbInformation
MsgBox "Open CCH. Once CCH Has Fully Loaded, Click the 'Import Into CCH' Button", vbInformation

Set fso = Nothing

End Sub