VBA - Remove Duplicate rows from an entire worksheet

[andreys]

Hi everyone!

I need help with VBA removing duplicate rows.

My colleagues are exporting reports from Power BI. Then, they are combined on a single worksheet. Each report can contain various numbers of rows - hundreds or dozens of thousands. After each report's dataset, an empty row is added, and then a message row with a list of filters applied in Power BI. You can see an example of the output file as attached. This is what combined data looks like.
Eventually, I don't need those "filter messages," but removing them is relatively easy. However, removing duplicate rows presents an issue.

I have recorded a macro in Excel. When recording the macro, I selected the entire worksheet, and it worked well in the workbook where it was recorded. But when running the same macro on a different dataset, it gives an error, specifically when the number of rows is larger.


Sub RemoveDuplicates()
Cells.Select
ActiveSheet.Range("$A$1:$V$5907").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6 _
, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22), Header:=xlYes
End Sub

Can you help me with the code that would remove duplicate rows from an entire worksheet without specifying the number of rows?

Thank you!!!
P.S. I am using Excel 365 for Windows, build 2307

[Paul_Hossler]

Probably something like this



Option Explicit


Sub RemoveDuplicates_1()
Dim r1 As Range, r2 As Range, r As Range


With ActiveSheet
Set r1 = .Cells(2, 1)
Set r2 = .Cells(.Rows.Count, 1).End(xlUp)
Set r = Range(r1, r2)

On Error Resume Next
r.SpecialCells(xlCellTypeConstants, xlTextValues).EntireRow.Delete
r.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0

.Cells(1, 1).CurrentRegion.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22), Header:=xlYes
End With

End Sub

[Aussiebear]

Welcome to VBAX Andreys.

[andreys]

I have found a solution:

Sub RemoveDuplicates()
Dim ws As Worksheet
Dim rng As Range
Dim n As Long
Dim i As Long
Set ws = ActiveSheet
Set rng = ws.UsedRange
n = rng.Columns.Count
ReDim varArray(0 To n - 1)
For i = 0 To n - 1
varArray(i) = i + 1
Next i
ws.UsedRange.RemoveDuplicates Columns:=(varArray), Header:=xlYes
End Sub