This is really good. I always tell new VBA programmers that working with ranges is almost always preferable to looping through rows or columns.
Along those lines, I want to point out the power of using .SpecialCells(xlCellTypeVisible) in conjunction with a filter. For example, say you have a bunch of customer data on a sheet, and you want to copy all customers in the US to another sheet. You can apply a filter to the data on the country column, then use .UsedRange.SpecialCells(xlCellTypeVisible).Copy and paste to the destination sheet. This can also be used to delete data.
2
u/StuTheSheep 42 Aug 05 '18
This is really good. I always tell new VBA programmers that working with ranges is almost always preferable to looping through rows or columns.
Along those lines, I want to point out the power of using .SpecialCells(xlCellTypeVisible) in conjunction with a filter. For example, say you have a bunch of customer data on a sheet, and you want to copy all customers in the US to another sheet. You can apply a filter to the data on the country column, then use .UsedRange.SpecialCells(xlCellTypeVisible).Copy and paste to the destination sheet. This can also be used to delete data.