Explicitly specify the columns argument and surround the array variable with parentheses. The documentation speaks of defaults and conveniences; do not believe it.
Range("A:A").RemoveDuplicates Columns:=(Array(1)), Headers:=xlYes
I’ve started using scratch tables, ephemeral views (if spreadsheets were single tables). I’ll spawn one at the end with Sheets.Add(), process it, and then delete it. It feels like each solution is subtly different, and I am gradually building a collection of useful subroutines. Things are getting cleaner, too.
The common elements I’ve been using are column enumerations, a sheet to act as a trace output console, and a lot of abuse with evaluations. Here I concatenate literal ampersand in a runtime formula with escaped double quotes with a UNC workbook path:
Cells(row_no,1) = "=VLOOKUP(J" & row_no & ", '\\net\path\[wb.xlsx]" & _ "Sheet1'!B:D, 3, 0)" & Chr(38) & """yes"""