I tried searching for "^f" which is a footnote reference but that won't work with wildcards so it may require some lateral thinking to do as a simple find and replace.
Since you are using VBA you might as well do the non-wildcard search and then work on that.
Code:
Sub FootnoteBracketsBegone()
Dim aRng As Range
Set aRng = ActiveDocument.Range
With aRng.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "^f)"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
Do While .Execute = True
If aRng.Characters.Last = ")" Then aRng.Characters.Last.Delete
aRng.End = ActiveDocument.Range.End
Loop
End With
End Sub