VBA Code for a Function to Delete Duplicate Rows based on Multiple Column Criteria

[OilEconomist]

Thanks in advance for your assistance

I am attempting to create a function which will delete duplicate rows based on multiple column criteria. Three issues are occurring.

(1) Delete Rows - How do I delete the rows as the rows are being cleared versus deleted.


(2) Column Array Dynamic - I want to check if they have duplicate values can vary, so I am using an Array. Keep in mind I would like the Array to be dynamic so I can use something like Array(1, 4, 5) in some instances or Array(1) in other instances. Right now, it gives me an error in the line of code within the function where the issue is "Columns:=Ary"

RngRmv.RemoveDuplicates Columns:=Ary, Header:=xlYes

When I change it to the following, it works: RngRmv.RemoveDuplicates Columns:=Array(1, 4, 5), Header:=xlYes


(3) Header - Be able to specify is it has a header or not through the function, but I get an error on the following code where the issue is "Header:=Hrd"

RngRmv.RemoveDuplicates Columns:=Array(1, 4, 5), Header:=Hrd

When I change it to the following, it works:

RngRmv.RemoveDuplicates Columns:=Array(1, 4, 5), Header:=xlYes

Sub TestRemoveDuplicatesF()

Dim ShtNm As String
ShtNm = ActiveSheet.name

Dim RowStart As Long
RowStart = 9

Dim RowEnd As Long
RowEnd = 19

Dim Ary() As Variant 'an array
Ary = Array(4, 5)

Dim Hdr As String
Hdr = "xlYes"
Dim RemoveDuplicates As Variant

RemoveDuplicates = RemoveDuplicatesF(ShtNm, RowStart, RowEnd, Ary, Hdr)


End Sub


'*************************************************************************************************************

Function RemoveDuplicatesF(ShtNm As String, RowStart As Long, RowEnd As Long, Ary As Variant, Hdr As String) As Variant

    Dim RngRmv As Range

    Set RngRmv = ActiveSheet.Range(RowStart & ":" & RowEnd)
    
    RngRmv.ClearOutline
    RngRmv.RemoveDuplicates Columns:=Ary, Header:=Hdr
    
End Function

The following is the fu