AdvancedFilter

Using the following code AdvancedFilter is working except where the filter is
set to 'D' on a particular column the returned set contains all rows where
that column starts with a 'D' i.e. D, Dad, Dolphin .... I need the returned
to be limited to the rows where column contains exactly 'D'.

Sub AllColumnsOneCustomer()
' Page 227
Dim IRange As Range
Dim ORange As Range
Dim CRange As Range

' Since this is called from a button on Menu,
' first select the sample data sheet
Worksheets("DBSize").Select
' Clear out results of previous macros
Range("G1:M50").EntireColumn.Delete

' 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
Cells(2, NextCol).Value = "D"
Set CRange = Cells(1, NextCol).Resize(2, 1)

' 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
' Range("L1").Select

End Sub

Change

Cells(2, NextCol).Value = "D"

to

Cells(2, NextCol).FormulaR1C1 = "=""=D"""

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"dannibrook" <[email protected]> wrote in message
news:[email protected]...
> Using the following code AdvancedFilter is working except where the filter
is
> set to 'D' on a particular column the returned set contains all rows where
> that column starts with a 'D' i.e. D, Dad, Dolphin .... I need the
returned
> to be limited to the rows where column contains exactly 'D'.
>
> Sub AllColumnsOneCustomer()
> ' Page 227
> Dim IRange As Range
> Dim ORange As Range
> Dim CRange As Range
>
> ' Since this is called from a button on Menu,
> ' first select the sample data sheet
> Worksheets("DBSize").Select
> ' Clear out results of previous macros
> Range("G1:M50").EntireColumn.Delete
>
> ' 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
> Cells(2, NextCol).Value = "D"
> Set CRange = Cells(1, NextCol).Resize(2, 1)
>
> ' 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
> ' Range("L1").Select
>
> End Sub

Bob, thank-you that works fine. How could I change the following more
generic code to do similar. The filter criteria is first picked up from the
line

ThisCust = cell.Value

I then need to adapt this in the following code so that the contents of
ThisCust are as you specified

' Set up the Criteria Range with one customer
Cells(1, NextCol + 2).Value = Range("D1").Value
Cells(2, NextCol + 2).Value = ThisCust


"Bob Phillips" wrote:

> Change
>
> Cells(2, NextCol).Value = "D"
>
> to
>
> Cells(2, NextCol).FormulaR1C1 = "=""=D"""
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "dannibrook" <[email protected]> wrote in message
> news:[email protected]...
> > Using the following code AdvancedFilter is working except where the filter
> is
> > set to 'D' on a particular column the returned set contains all rows where
> > that column starts with a 'D' i.e. D, Dad, Dolphin .... I need the
> returned
> > to be limited to the rows where column contains exactly 'D'.
> >
> > Sub AllColumnsOneCustomer()
> > ' Page 227
> > Dim IRange As Range
> > Dim ORange As Range
> > Dim CRange As Range
> >
> > ' Since this is called from a button on Menu,
> > ' first select the sample data sheet
> > Worksheets("DBSize").Select
> > ' Clear out results of previous macros
> > Range("G1:M50").EntireColumn.Delete
> >
> > ' 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
> > Cells(2, NextCol).Value = "D"
> > Set CRange = Cells(1, NextCol).Resize(2, 1)
> >
> > ' 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
> > ' Range("L1").Select
> >
> > End Sub
>
>
>

Cells(2, NextCol + 2).Value = "=""=" & ThisCust & """"

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"dannibrook" <[email protected]> wrote in message
news:[email protected]...
> Bob, thank-you that works fine. How could I change the following more
> generic code to do similar. The filter criteria is first picked up from
the
> line
>
> ThisCust = cell.Value
>
> I then need to adapt this in the following code so that the contents of
> ThisCust are as you specified
>
> ' Set up the Criteria Range with one customer
> Cells(1, NextCol + 2).Value = Range("D1").Value
> Cells(2, NextCol + 2).Value = ThisCust
>
>
> "Bob Phillips" wrote:
>
> > Change
> >
> > Cells(2, NextCol).Value = "D"
> >
> > to
> >
> > Cells(2, NextCol).FormulaR1C1 = "=""=D"""
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "dannibrook" <[email protected]> wrote in message
> > news:[email protected]...
> > > Using the following code AdvancedFilter is working except where the
filter
> > is
> > > set to 'D' on a particular column the returned set contains all rows
where
> > > that column starts with a 'D' i.e. D, Dad, Dolphin .... I need the
> > returned
> > > to be limited to the rows where column contains exactly 'D'.
> > >
> > > Sub AllColumnsOneCustomer()
> > > ' Page 227
> > > Dim IRange As Range
> > > Dim ORange As Range
> > > Dim CRange As Range
> > >
> > > ' Since this is called from a button on Menu,
> > > ' first select the sample data sheet
> > > Worksheets("DBSize").Select
> > > ' Clear out results of previous macros
> > > Range("G1:M50").EntireColumn.Delete
> > >
> > > ' 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
> > > Cells(2, NextCol).Value = "D"
> > > Set CRange = Cells(1, NextCol).Resize(2, 1)
> > >
> > > ' 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
> > > ' Range("L1").Select
> > >
> > > End Sub
> >
> >
> >

marvellous, thank-you once again

"Bob Phillips" wrote:

> Cells(2, NextCol + 2).Value = "=""=" & ThisCust & """"
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "dannibrook" <[email protected]> wrote in message
> news:[email protected]...
> > Bob, thank-you that works fine. How could I change the following more
> > generic code to do similar. The filter criteria is first picked up from
> the
> > line
> >
> > ThisCust = cell.Value
> >
> > I then need to adapt this in the following code so that the contents of
> > ThisCust are as you specified
> >
> > ' Set up the Criteria Range with one customer
> > Cells(1, NextCol + 2).Value = Range("D1").Value
> > Cells(2, NextCol + 2).Value = ThisCust
> >
> >
> > "Bob Phillips" wrote:
> >
> > > Change
> > >
> > > Cells(2, NextCol).Value = "D"
> > >
> > > to
> > >
> > > Cells(2, NextCol).FormulaR1C1 = "=""=D"""
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from email address if mailing direct)
> > >
> > > "dannibrook" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Using the following code AdvancedFilter is working except where the
> filter
> > > is
> > > > set to 'D' on a particular column the returned set contains all rows
> where
> > > > that column starts with a 'D' i.e. D, Dad, Dolphin .... I need the
> > > returned
> > > > to be limited to the rows where column contains exactly 'D'.
> > > >
> > > > Sub AllColumnsOneCustomer()
> > > > ' Page 227
> > > > Dim IRange As Range
> > > > Dim ORange As Range
> > > > Dim CRange As Range
> > > >
> > > > ' Since this is called from a button on Menu,
> > > > ' first select the sample data sheet
> > > > Worksheets("DBSize").Select
> > > > ' Clear out results of previous macros
> > > > Range("G1:M50").EntireColumn.Delete
> > > >
> > > > ' 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
> > > > Cells(2, NextCol).Value = "D"
> > > > Set CRange = Cells(1, NextCol).Resize(2, 1)
> > > >
> > > > ' 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
> > > > ' Range("L1").Select
> > > >
> > > > End Sub
> > >
> > >
> > >
>
>
>