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.
Can you help me with the code that would remove duplicate rows from an entire worksheet without specifying the number of rows?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
Thank you!!!
P.S. I am using Excel 365 for Windows, build 2307