Help with understanding a remove duplicate macro

[Taiba]

Hello,
I'm new to VBA and had searched through the forums and found the following macro than can be used to delete duplicate rows where the range is dynamic. would you explain to me how does this macro work? what is the meaning of this in particular (strRange = "$A$1:" & ActiveCell.SpecialCells(xlLastCell).Address)


strRange = "$A$1:" & ActiveCell.SpecialCells(xlLastCell).Address
ActiveSheet.Range(strRange).RemoveDuplicates

Thanks

 

[Joe4]

Welcome to the Board!

The first line is just building a string to represent the range you want to apply this to.
"ActiveCell.SpecialCells(xlLastCell)" is the same as pressing CTRL-END on your Excel sheet. That will jump to the last cell with data (or held data).
The ".Address" part just returns the address of that cell.
So if that cell was Z100, the value of strRange would be: "$A$1:$Z$100"

Note that ranges in VBA are denoted like this:
Range("A1")
Range("$A1:$M100)
etc

So, all we are doing is turning that strange into a real range, and then applying the "Remove Duplicates" functionality to it.
Note that the newer version of Excel have "Remove Duplicates" on the Data menu.

Also note that you can get the VBA code for some basic actions you can perform in Access by turning on the Macro Recorder, and recording yourself performing actions, like removing duplicates. This is a good tool for beginners, and for experts alike, as we do not necessarily need to remember the exact structure of certain VBA commands if we can get that part by using the Macro Recorder.

I hope that helps. If you have other questions about this code, please let us know below.

 

[Taiba]

Thank you joe4. This is extremly helpful.

 

[Joe4]

You are welcome!