Copying All Columns

To copy all columns, specify a single blank cell as the output range. You will get all columns for those records that match the criteria as shown in Figure 11.17.

Sub AllColumnsOneCustomer() Dim IRange As Range Dim ORange As Range Dim CRange As Range

1 Find the size of today's dataset FinalRow = Cells(65536, 1).End(xlUp).Row NextCol = Cells(1, 255).End(xlToLeft).Column + 2

' Set up the criteria range with one customer

Cells(1, NextCol).Value = Range("D1").Value

' In reality, this value should be passed from the userform

' Set up output range. It is a single blank cell Set ORange = Cells(1, NextCol + 2)

' Define the Input Range

Set IRange = Range("A1").Resize(FinalRow, NextCol - 2)

' Do the Advanced Filter to get unique list of customers & product IRange.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=CRange, ^CopyToRange:=ORange

End Sub

Figure 11.17

When using xlFilterCopy with a blank output range, you get all columns in the same order as they appear in the original list range. If you are taking the data to another report,you may want only a subset of these columns and in a differ-

0 0

Post a comment