andreys
10-07-2023, 12:57 PM
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
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