Alright here is a stumper for me, hopefully you guys can help. What i have is
a list of lamptypes and how many they are per light fixture. Then i want to
sum all of them to a summary column. I have the columns with sumif's to add
only the particular lamptype chosen. What i am trying to do is use the
advancefilter in a macro.
What i want is when i get done with my huge list of lamptypes is have the
macro pick up all the unique types and display them in a list. The problem
that is occuring is that the first lamp always repeats itself for some
reason.

Here is the VB Macro script:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 12/14/2005 by Andrew Slater
'
' Keyboard Shortcut: Ctrl+Shift+L
'
Range("LAMPTYPE").AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range( _
"AB5"), Unique:=True
End Sub

I can also send you a copy of the spreadsheet if you can really help me.
Just attach an email with your post.

Andy

Add a header to your list. Advanced filter is picking up the first entry in
your data as the header--not as a real value.

Andy wrote:

Alright here is a stumper for me, hopefully you guys can help. What i have is
a list of lamptypes and how many they are per light fixture. Then i want to
sum all of them to a summary column. I have the columns with sumif's to add
only the particular lamptype chosen. What i am trying to do is use the
advancefilter in a macro.
What i want is when i get done with my huge list of lamptypes is have the
macro pick up all the unique types and display them in a list. The problem
that is occuring is that the first lamp always repeats itself for some
reason.

Here is the VB Macro script:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 12/14/2005 by Andrew Slater
'
' Keyboard Shortcut: Ctrl+Shift+L
'
Range("LAMPTYPE").AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range( _
"AB5"), Unique:=True
End Sub

I can also send you a copy of the spreadsheet if you can really help me.
Just attach an email with your post.

Andy

--

Dave Peterson

Is it possible that there is a spelling discrepancy or a trailing or leading
space for one of the two values?
After you extract them, try something like =AB5=AB6 (or whatever cells the
two repeated values are in). If it comes back false, Excel is not
recognizing them as the same thing.

For what's it's worth, your macro worked fine on the dummy data I created.

--
tj


"Andy" wrote:

Alright here is a stumper for me, hopefully you guys can help. What i have is
a list of lamptypes and how many they are per light fixture. Then i want to
sum all of them to a summary column. I have the columns with sumif's to add
only the particular lamptype chosen. What i am trying to do is use the
advancefilter in a macro.
What i want is when i get done with my huge list of lamptypes is have the
macro pick up all the unique types and display them in a list. The problem
that is occuring is that the first lamp always repeats itself for some
reason.

Here is the VB Macro script:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 12/14/2005 by Andrew Slater
'
' Keyboard Shortcut: Ctrl+Shift+L
'
Range("LAMPTYPE").AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range( _
"AB5"), Unique:=True
End Sub

I can also send you a copy of the spreadsheet if you can really help me.
Just attach an email with your post.

Andy

Try deleting the entire extract range and then run the macro again. If that
doesn't work, please describe the layout and the steps more thoroughly.

--
tj


"Andy" wrote:

Thanks guys, it makes sense but now i am getting a error that says "The
extract range has a missing or illegal field name"

"tjtjjtjt" wrote:

One more thought. If your Named Range does not include a Column Heading,
Excel is reading the top value in the list as a column heading. So it's not
exactly repeating - it's showing up as the Column Header and as an item in
the column.

--
tj


"Andy" wrote:

Alright here is a stumper for me, hopefully you guys can help. What i have is
a list of lamptypes and how many they are per light fixture. Then i want to
sum all of them to a summary column. I have the columns with sumif's to add
only the particular lamptype chosen. What i am trying to do is use the
advancefilter in a macro.
What i want is when i get done with my huge list of lamptypes is have the
macro pick up all the unique types and display them in a list. The problem
that is occuring is that the first lamp always repeats itself for some
reason.

Here is the VB Macro script:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 12/14/2005 by Andrew Slater
'
' Keyboard Shortcut: Ctrl+Shift+L
'
Range("LAMPTYPE").AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range( _
"AB5"), Unique:=True
End Sub

I can also send you a copy of the spreadsheet if you can really help me.
Just attach an email with your post.

Andy

Thanks guys, it makes sense but now i am getting a error that says "The
extract range has a missing or illegal field name"

"tjtjjtjt" wrote:

One more thought. If your Named Range does not include a Column Heading,
Excel is reading the top value in the list as a column heading. So it's not
exactly repeating - it's showing up as the Column Header and as an item in
the column.

--
tj


"Andy" wrote:

Alright here is a stumper for me, hopefully you guys can help. What i have is
a list of lamptypes and how many they are per light fixture. Then i want to
sum all of them to a summary column. I have the columns with sumif's to add
only the particular lamptype chosen. What i am trying to do is use the
advancefilter in a macro.
What i want is when i get done with my huge list of lamptypes is have the
macro pick up all the unique types and display them in a list. The problem
that is occuring is that the first lamp always repeats itself for some
reason.

Here is the VB Macro script:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 12/14/2005 by Andrew Slater
'
' Keyboard Shortcut: Ctrl+Shift+L
'
Range("LAMPTYPE").AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range( _
"AB5"), Unique:=True
End Sub

I can also send you a copy of the spreadsheet if you can really help me.
Just attach an email with your post.

Andy

One more thought. If your Named Range does not include a Column Heading,
Excel is reading the top value in the list as a column heading. So it's not
exactly repeating - it's showing up as the Column Header and as an item in
the column.

--
tj


"Andy" wrote:

Alright here is a stumper for me, hopefully you guys can help. What i have is
a list of lamptypes and how many they are per light fixture. Then i want to
sum all of them to a summary column. I have the columns with sumif's to add
only the particular lamptype chosen. What i am trying to do is use the
advancefilter in a macro.
What i want is when i get done with my huge list of lamptypes is have the
macro pick up all the unique types and display them in a list. The problem
that is occuring is that the first lamp always repeats itself for some
reason.

Here is the VB Macro script:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 12/14/2005 by Andrew Slater
'
' Keyboard Shortcut: Ctrl+Shift+L
'
Range("LAMPTYPE").AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range( _
"AB5"), Unique:=True
End Sub

I can also send you a copy of the spreadsheet if you can really help me.
Just attach an email with your post.

Andy

Sorry for getting back so late, but i will try to describe the layout better.

Column 'R' is my Lamptype column; Cell R1 is blank, Cell R2 is the heading
"LAMP TYPE",cells R3 & R4 are blank. Cells R5 and down are all my lamps. I
"LAMPTYPE"(Used in the formula/macro) defined in cells R5 and down to
automatically offset one cell down so i dont have to update list all the time.

Now i would like to use the advanced autofilter to move all the unique
lamptypes, w/o repeating them, into column T starting on cell T5. So the list
of lamptype should start on T5 and go down.

When it sorta works the first couple cells are the same, and the result is
that extracting point repeats the first row. If the first couple cells are
different then i get a error message "The extract range has a missing or
illegal field name"

I am going to give my old school email out in hope you can email me back so
i can then email you back the spreadsheet i am creating.

My old school email is

"tjtjjtjt" wrote:

Try deleting the entire extract range and then run the macro again. If that
doesn't work, please describe the layout and the steps more thoroughly.

--
tj


"Andy" wrote:

Thanks guys, it makes sense but now i am getting a error that says "The
extract range has a missing or illegal field name"

"tjtjjtjt" wrote:

One more thought. If your Named Range does not include a Column Heading,
Excel is reading the top value in the list as a column heading. So it's not
exactly repeating - it's showing up as the Column Header and as an item in
the column.

--
tj


"Andy" wrote:

Alright here is a stumper for me, hopefully you guys can help. What i have is
a list of lamptypes and how many they are per light fixture. Then i want to
sum all of them to a summary column. I have the columns with sumif's to add
only the particular lamptype chosen. What i am trying to do is use the
advancefilter in a macro.
What i want is when i get done with my huge list of lamptypes is have the
macro pick up all the unique types and display them in a list. The problem
that is occuring is that the first lamp always repeats itself for some
reason.

Here is the VB Macro script:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 12/14/2005 by Andrew Slater
'
' Keyboard Shortcut: Ctrl+Shift+L
'
Range("LAMPTYPE").AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range( _
"AB5"), Unique:=True
End Sub

I can also send you a copy of the spreadsheet if you can really help me.
Just attach an email with your post.

Andy