Hi All........
Anyone know if it's possible to programmatically turn off the DownArrows on
the display while leaving the Autofilter in it's filtered state?.....
I would like for my user to see only the Autofiltered results, and not be
aware that they can reorder them with the downarrows.
Otherwise, can I call my autofilter with code, then freeze it so the
downarrows do not work manually until I release them with more code.

TIA
Vaya con Dios,
Chuck, CABGx3

Debra Dalgleish knows how:
http://contextures.com/xlautofilter03.html#Hide

CLR wrote:

Hi All........
Anyone know if it's possible to programmatically turn off the DownArrows on
the display while leaving the Autofilter in it's filtered state?.....
I would like for my user to see only the Autofiltered results, and not be
aware that they can reorder them with the downarrows.
Otherwise, can I call my autofilter with code, then freeze it so the
downarrows do not work manually until I release them with more code.

TIA
Vaya con Dios,
Chuck, CABGx3

--

Dave Peterson

That's cool Dave, thanks, it basically works, but I have a little problem
with it in my XL97

the following returns an error of "1004 Autofilter method of range class
failed"

c.AutoFilter Field:=c.Column, _
Visibledropdown:=False

Any idea how to fix?

Vaya con Dios,
Chuck, CABGx3



"Dave Peterson" wrote in message
...
Debra Dalgleish knows how:
http://contextures.com/xlautofilter03.html#Hide

CLR wrote:

Hi All........
Anyone know if it's possible to programmatically turn off the DownArrows
on
the display while leaving the Autofilter in it's filtered state?.....
I would like for my user to see only the Autofiltered results, and not
be
aware that they can reorder them with the downarrows.
Otherwise, can I call my autofilter with code, then freeze it so the
downarrows do not work manually until I release them with more code.

TIA
Vaya con Dios,
Chuck, CABGx3

--

Dave Peterson

I think it could be lots of things--from worksheet protection to what c is.

Maybe more code and more description about your data--did you apply filters
already or what?

CLR wrote:

That's cool Dave, thanks, it basically works, but I have a little problem
with it in my XL97

the following returns an error of "1004 Autofilter method of range class
failed"

c.AutoFilter Field:=c.Column, _
Visibledropdown:=False

Any idea how to fix?

Vaya con Dios,
Chuck, CABGx3

"Dave Peterson" wrote in message
...
Debra Dalgleish knows how:
http://contextures.com/xlautofilter03.html#Hide

CLR wrote:

Hi All........
Anyone know if it's possible to programmatically turn off the DownArrows
on
the display while leaving the Autofilter in it's filtered state?.....
I would like for my user to see only the Autofiltered results, and not
be
aware that they can reorder them with the downarrows.
Otherwise, can I call my autofilter with code, then freeze it so the
downarrows do not work manually until I release them with more code.

TIA
Vaya con Dios,
Chuck, CABGx3

--

Dave Peterson

--

Dave Peterson

Hi Chuck,
The "VisibleDropDown" option is not available in XL97 for the AutoFilter Method.
I don't know how to work around that at the present.
Regards,
Jim Cone



"CLR"
wrote in message
That's cool Dave, thanks, it basically works, but I have a little problem
with it in my XL97
the following returns an error of "1004 Autofilter method of range class
failed"

c.AutoFilter Field:=c.Column, _
Visibledropdown:=False

Any idea how to fix?
Vaya con Dios,
Chuck, CABGx3

Thanks for the info Jim........although it "does" make the arrows go away,
it's just that I get the error message and have to click it off.........

Vaya con Dios,
Chuck, CABGx3


"Jim Cone" wrote in message
...

Hi Chuck,
The "VisibleDropDown" option is not available in XL97 for the AutoFilter
Method.
I don't know how to work around that at the present.
Regards,
Jim Cone



"CLR"
wrote in message
That's cool Dave, thanks, it basically works, but I have a little problem
with it in my XL97
the following returns an error of "1004 Autofilter method of range class
failed"

c.AutoFilter Field:=c.Column, _
Visibledropdown:=False

Any idea how to fix?
Vaya con Dios,
Chuck, CABGx3

Here's the whole code.


Sub HideArrows()
'hides all arrows except column 2
Dim c As Range
Dim i As Integer
i = Cells(1, 1).End(xlToRight).Column
Application.ScreenUpdating = False

For Each c In Range(Cells(1, 1), Cells(1, i))
If c.Column < 2 Then
c.AutoFilter Field:=c.Column, _
Visibledropdown:=False
End If
Next

Application.ScreenUpdating = True
End Sub

If I have Autofilter applied and then run this macro, it gets down to the
errant part and gives the error message, then when I click "Debug" it goes
ahead and hides the arrows...........but leaves me hung up on that line of
code.

Vaya con Dios,
Chuck, CABGx3


"Dave Peterson" wrote in message
...
I think it could be lots of things--from worksheet protection to what c
is.

Maybe more code and more description about your data--did you apply
filters
already or what?

CLR wrote:

That's cool Dave, thanks, it basically works, but I have a little
problem
with it in my XL97

the following returns an error of "1004 Autofilter method of range class
failed"

c.AutoFilter Field:=c.Column, _
Visibledropdown:=False

Any idea how to fix?

Vaya con Dios,
Chuck, CABGx3

"Dave Peterson" wrote in message
...
Debra Dalgleish knows how:
http://contextures.com/xlautofilter03.html#Hide

CLR wrote:

Hi All........
Anyone know if it's possible to programmatically turn off the
DownArrows
on
the display while leaving the Autofilter in it's filtered
state?.....
I would like for my user to see only the Autofiltered results, and
not
be
aware that they can reorder them with the downarrows.
Otherwise, can I call my autofilter with code, then freeze it so the
downarrows do not work manually until I release them with more code.

TIA
Vaya con Dios,
Chuck, CABGx3

--

Dave Peterson

--

Dave Peterson

Ok guys, I think I got it now............this seems to work

Sub HideArrows()
Dim c As Range
Dim i As Integer
On Error Resume Next
i = Cells(1, 1).End(xlToRight).Column
Application.ScreenUpdating = False
For Each c In Range(Cells(1, 1), Cells(1, i))
c.AutoFilter Field:=c.Column, _
Visibledropdown:=False
Next
Application.ScreenUpdating = True
End Sub

Thanks for all your responses.........

Vaya con Dios,
Chuck, CABGx3


"CLR" wrote in message
...
Hi All........
Anyone know if it's possible to programmatically turn off the DownArrows
on
the display while leaving the Autofilter in it's filtered state?.....
I would like for my user to see only the Autofiltered results, and not be
aware that they can reorder them with the downarrows.
Otherwise, can I call my autofilter with code, then freeze it so the
downarrows do not work manually until I release them with more code.

TIA
Vaya con Dios,
Chuck, CABGx3

Well, I "thought" it was working during testing, but when I worked it in I
found that it did make the arrows go away, but also turned off the whole
Autofilter.........bummer. Guess I'll have to move up a notch in Excel
versions.

Thanks anyway to all
Vaya con Dios,
Chuck, CABGx3



"CLR" wrote in message
...
Hi All........
Anyone know if it's possible to programmatically turn off the DownArrows
on
the display while leaving the Autofilter in it's filtered state?.....
I would like for my user to see only the Autofiltered results, and not be
aware that they can reorder them with the downarrows.
Otherwise, can I call my autofilter with code, then freeze it so the
downarrows do not work manually until I release them with more code.

TIA
Vaya con Dios,
Chuck, CABGx3

Chuck,
In post XL 97 versions, it appears you can remove individual arrows from
the AutoFilter range. However, if you remove the arrow on the filtered field
the entire range is displayed again.
Regards,
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"CLR"
wrote in message
Well, I "thought" it was working during testing, but when I worked it in I
found that it did make the arrows go away, but also turned off the whole
Autofilter.........bummer. Guess I'll have to move up a notch in Excel
versions.
Thanks anyway to all
Vaya con Dios,
Chuck, CABGx3



"CLR"
wrote in message
Hi All........
Anyone know if it's possible to programmatically turn off the DownArrows
on
the display while leaving the Autofilter in it's filtered state?.....
I would like for my user to see only the Autofiltered results, and not be
aware that they can reorder them with the downarrows.
Otherwise, can I call my autofilter with code, then freeze it so the
downarrows do not work manually until I release them with more code.

TIA
Vaya con Dios,
Chuck, CABGx3

Thanks Jim......
Yeah, that's where I'm at now on 97..........bummer if it can't be on any
version.........guess I'll have to cheat then.....<g

Vaya con Dios,
Chuck, CABGx3


"Jim Cone" wrote in message
...

Chuck,
In post XL 97 versions, it appears you can remove individual arrows from
the AutoFilter range. However, if you remove the arrow on the filtered
field
the entire range is displayed again.
Regards,
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"CLR"
wrote in message
Well, I "thought" it was working during testing, but when I worked it in I
found that it did make the arrows go away, but also turned off the whole
Autofilter.........bummer. Guess I'll have to move up a notch in Excel
versions.
Thanks anyway to all
Vaya con Dios,
Chuck, CABGx3



"CLR"
wrote in message
Hi All........
Anyone know if it's possible to programmatically turn off the DownArrows
on
the display while leaving the Autofilter in it's filtered state?.....
I would like for my user to see only the Autofiltered results, and not
be
aware that they can reorder them with the downarrows.
Otherwise, can I call my autofilter with code, then freeze it so the
downarrows do not work manually until I release them with more code.

TIA
Vaya con Dios,
Chuck, CABGx3

I don't remember enough about xl97 to know about visibledropdown not being
there.

But in xl2k+ you could use this to be more specific about the range:

Sub HideArrows()
'hides all arrows except column 2
Dim c As Range
Dim i As Long
Application.ScreenUpdating = False

i = 0
For Each c In activesheet.autofilter.range.rows(1).cells
i = i + 1
if i < 2 then
c.AutoFilter Field:=i, Visibledropdown:=False
End If
Next c

Application.ScreenUpdating = True
End Sub

Then you don't have to worry about what row and what column the autofilter range
starts.


CLR wrote:

Here's the whole code.

Sub HideArrows()
'hides all arrows except column 2
Dim c As Range
Dim i As Integer
i = Cells(1, 1).End(xlToRight).Column
Application.ScreenUpdating = False

For Each c In Range(Cells(1, 1), Cells(1, i))
If c.Column < 2 Then
c.AutoFilter Field:=c.Column, _
Visibledropdown:=False
End If
Next

Application.ScreenUpdating = True
End Sub

If I have Autofilter applied and then run this macro, it gets down to the
errant part and gives the error message, then when I click "Debug" it goes
ahead and hides the arrows...........but leaves me hung up on that line of
code.

Vaya con Dios,
Chuck, CABGx3

"Dave Peterson" wrote in message
...
I think it could be lots of things--from worksheet protection to what c
is.

Maybe more code and more description about your data--did you apply
filters
already or what?

CLR wrote:

That's cool Dave, thanks, it basically works, but I have a little
problem
with it in my XL97

the following returns an error of "1004 Autofilter method of range class
failed"

c.AutoFilter Field:=c.Column, _
Visibledropdown:=False

Any idea how to fix?

Vaya con Dios,
Chuck, CABGx3

"Dave Peterson" wrote in message
...
Debra Dalgleish knows how:
http://contextures.com/xlautofilter03.html#Hide

CLR wrote:

Hi All........
Anyone know if it's possible to programmatically turn off the
DownArrows
on
the display while leaving the Autofilter in it's filtered
state?.....
I would like for my user to see only the Autofiltered results, and
not
be
aware that they can reorder them with the downarrows.
Otherwise, can I call my autofilter with code, then freeze it so the
downarrows do not work manually until I release them with more code.

TIA
Vaya con Dios,
Chuck, CABGx3

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Thanks for posting the clarification, Jim.

Jim Cone wrote:


Hi Chuck,
The "VisibleDropDown" option is not available in XL97 for the AutoFilter Method.
I don't know how to work around that at the present.
Regards,
Jim Cone

"CLR"
wrote in message
That's cool Dave, thanks, it basically works, but I have a little problem
with it in my XL97
the following returns an error of "1004 Autofilter method of range class
failed"

c.AutoFilter Field:=c.Column, _
Visibledropdown:=False

Any idea how to fix?
Vaya con Dios,
Chuck, CABGx3

--

Dave Peterson

Thanks Dave, but it does the same thing about killing the Autofilter when it
kills the arrow on the filtered column..........guess I'll just have to
cheat <g

Vaya con Dios,
Chuck, CABGx3


"Dave Peterson" wrote in message
...
I don't remember enough about xl97 to know about visibledropdown not being
there.

But in xl2k+ you could use this to be more specific about the range:

Sub HideArrows()
'hides all arrows except column 2
Dim c As Range
Dim i As Long
Application.ScreenUpdating = False

i = 0
For Each c In activesheet.autofilter.range.rows(1).cells
i = i + 1
if i < 2 then
c.AutoFilter Field:=i, Visibledropdown:=False
End If
Next c

Application.ScreenUpdating = True
End Sub

Then you don't have to worry about what row and what column the autofilter
range
starts.


CLR wrote:

Here's the whole code.

Sub HideArrows()
'hides all arrows except column 2
Dim c As Range
Dim i As Integer
i = Cells(1, 1).End(xlToRight).Column
Application.ScreenUpdating = False

For Each c In Range(Cells(1, 1), Cells(1, i))
If c.Column < 2 Then
c.AutoFilter Field:=c.Column, _
Visibledropdown:=False
End If
Next

Application.ScreenUpdating = True
End Sub

If I have Autofilter applied and then run this macro, it gets down to
the
errant part and gives the error message, then when I click "Debug" it
goes
ahead and hides the arrows...........but leaves me hung up on that line
of
code.

Vaya con Dios,
Chuck, CABGx3

"Dave Peterson" wrote in message
...
I think it could be lots of things--from worksheet protection to what
c
is.

Maybe more code and more description about your data--did you apply
filters
already or what?

CLR wrote:

That's cool Dave, thanks, it basically works, but I have a little
problem
with it in my XL97

the following returns an error of "1004 Autofilter method of range
class
failed"

c.AutoFilter Field:=c.Column, _
Visibledropdown:=False

Any idea how to fix?

Vaya con Dios,
Chuck, CABGx3

"Dave Peterson" wrote in message
...
Debra Dalgleish knows how:
http://contextures.com/xlautofilter03.html#Hide

CLR wrote:

Hi All........
Anyone know if it's possible to programmatically turn off the
DownArrows
on
the display while leaving the Autofilter in it's filtered
state?.....
I would like for my user to see only the Autofiltered results,
and
not
be
aware that they can reorder them with the downarrows.
Otherwise, can I call my autofilter with code, then freeze it so
the
downarrows do not work manually until I release them with more
code.

TIA
Vaya con Dios,
Chuck, CABGx3

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

The code wasn't an attempt to get by the arrows showing up (or not showing up),
it was just to show you a way not to have to rely on the data being in A1
through some column in row 1.

Maybe you could add a row under the header row -- keep it empty. Then apply the
filter to that row and the data below. And hide that empty row with the arrows.

CLR wrote:

Thanks Dave, but it does the same thing about killing the Autofilter when it
kills the arrow on the filtered column..........guess I'll just have to
cheat <g

Vaya con Dios,
Chuck, CABGx3

"Dave Peterson" wrote in message
...
I don't remember enough about xl97 to know about visibledropdown not being
there.

But in xl2k+ you could use this to be more specific about the range:

Sub HideArrows()
'hides all arrows except column 2
Dim c As Range
Dim i As Long
Application.ScreenUpdating = False

i = 0
For Each c In activesheet.autofilter.range.rows(1).cells
i = i + 1
if i < 2 then
c.AutoFilter Field:=i, Visibledropdown:=False
End If
Next c

Application.ScreenUpdating = True
End Sub

Then you don't have to worry about what row and what column the autofilter
range
starts.


CLR wrote:

Here's the whole code.

Sub HideArrows()
'hides all arrows except column 2
Dim c As Range
Dim i As Integer
i = Cells(1, 1).End(xlToRight).Column
Application.ScreenUpdating = False

For Each c In Range(Cells(1, 1), Cells(1, i))
If c.Column < 2 Then
c.AutoFilter Field:=c.Column, _
Visibledropdown:=False
End If
Next

Application.ScreenUpdating = True
End Sub

If I have Autofilter applied and then run this macro, it gets down to
the
errant part and gives the error message, then when I click "Debug" it
goes
ahead and hides the arrows...........but leaves me hung up on that line
of
code.

Vaya con Dios,
Chuck, CABGx3

"Dave Peterson" wrote in message
...
I think it could be lots of things--from worksheet protection to what
c
is.

Maybe more code and more description about your data--did you apply
filters
already or what?

CLR wrote:

That's cool Dave, thanks, it basically works, but I have a little
problem
with it in my XL97

the following returns an error of "1004 Autofilter method of range
class
failed"

c.AutoFilter Field:=c.Column, _
Visibledropdown:=False

Any idea how to fix?

Vaya con Dios,
Chuck, CABGx3

"Dave Peterson" wrote in message
...
Debra Dalgleish knows how:
http://contextures.com/xlautofilter03.html#Hide

CLR wrote:

Hi All........
Anyone know if it's possible to programmatically turn off the
DownArrows
on
the display while leaving the Autofilter in it's filtered
state?.....
I would like for my user to see only the Autofiltered results,
and
not
be
aware that they can reorder them with the downarrows.
Otherwise, can I call my autofilter with code, then freeze it so
the
downarrows do not work manually until I release them with more
code.

TIA
Vaya con Dios,
Chuck, CABGx3

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

NOW THATS WHAT I'M TALKING ABOUT!!!!!

A good old-fashioned "cheat".
I was gonna overlay the arrow row with a picture of itself, but this is a
lot easier.

Thanks Dave, that'll do me.

Vaya con Dios,
Chuck, CABGx3


"Dave Peterson" wrote in message
...
The code wasn't an attempt to get by the arrows showing up (or not showing
up),
it was just to show you a way not to have to rely on the data being in A1
through some column in row 1.

Maybe you could add a row under the header row -- keep it empty. Then
apply the
filter to that row and the data below. And hide that empty row with the
arrows.

CLR wrote:

Thanks Dave, but it does the same thing about killing the Autofilter
when it
kills the arrow on the filtered column..........guess I'll just have to
cheat <g

Vaya con Dios,
Chuck, CABGx3

"Dave Peterson" wrote in message
...
I don't remember enough about xl97 to know about visibledropdown not
being
there.

But in xl2k+ you could use this to be more specific about the range:

Sub HideArrows()
'hides all arrows except column 2
Dim c As Range
Dim i As Long
Application.ScreenUpdating = False

i = 0
For Each c In activesheet.autofilter.range.rows(1).cells
i = i + 1
if i < 2 then
c.AutoFilter Field:=i, Visibledropdown:=False
End If
Next c

Application.ScreenUpdating = True
End Sub

Then you don't have to worry about what row and what column the
autofilter
range
starts.


CLR wrote:

Here's the whole code.

Sub HideArrows()
'hides all arrows except column 2
Dim c As Range
Dim i As Integer
i = Cells(1, 1).End(xlToRight).Column
Application.ScreenUpdating = False

For Each c In Range(Cells(1, 1), Cells(1, i))
If c.Column < 2 Then
c.AutoFilter Field:=c.Column, _
Visibledropdown:=False
End If
Next

Application.ScreenUpdating = True
End Sub

If I have Autofilter applied and then run this macro, it gets down
to
the
errant part and gives the error message, then when I click "Debug"
it
goes
ahead and hides the arrows...........but leaves me hung up on that
line
of
code.

Vaya con Dios,
Chuck, CABGx3

"Dave Peterson" wrote in message
...
I think it could be lots of things--from worksheet protection to
what
c
is.

Maybe more code and more description about your data--did you
apply
filters
already or what?

CLR wrote:

That's cool Dave, thanks, it basically works, but I have a
little
problem
with it in my XL97

the following returns an error of "1004 Autofilter method of
range
class
failed"

c.AutoFilter Field:=c.Column, _
Visibledropdown:=False

Any idea how to fix?

Vaya con Dios,
Chuck, CABGx3

"Dave Peterson" wrote in message
...
Debra Dalgleish knows how:
http://contextures.com/xlautofilter03.html#Hide

CLR wrote:

Hi All........
Anyone know if it's possible to programmatically turn off
the
DownArrows
on
the display while leaving the Autofilter in it's filtered
state?.....
I would like for my user to see only the Autofiltered
results,
and
not
be
aware that they can reorder them with the downarrows.
Otherwise, can I call my autofilter with code, then freeze
it so
the
downarrows do not work manually until I release them with
more
code.

TIA
Vaya con Dios,
Chuck, CABGx3

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

I hate inserting blank rows. It just ruins the table structure of the data (I
think).

But if you really can't have, er, don't want arrows, why not?

CLR wrote:

NOW THATS WHAT I'M TALKING ABOUT!!!!!

A good old-fashioned "cheat".
I was gonna overlay the arrow row with a picture of itself, but this is a
lot easier.

Thanks Dave, that'll do me.

Vaya con Dios,
Chuck, CABGx3

"Dave Peterson" wrote in message
...
The code wasn't an attempt to get by the arrows showing up (or not showing
up),
it was just to show you a way not to have to rely on the data being in A1
through some column in row 1.

Maybe you could add a row under the header row -- keep it empty. Then
apply the
filter to that row and the data below. And hide that empty row with the
arrows.

CLR wrote:

Thanks Dave, but it does the same thing about killing the Autofilter
when it
kills the arrow on the filtered column..........guess I'll just have to
cheat <g

Vaya con Dios,
Chuck, CABGx3

"Dave Peterson" wrote in message
...
I don't remember enough about xl97 to know about visibledropdown not
being
there.

But in xl2k+ you could use this to be more specific about the range:

Sub HideArrows()
'hides all arrows except column 2
Dim c As Range
Dim i As Long
Application.ScreenUpdating = False

i = 0
For Each c In activesheet.autofilter.range.rows(1).cells
i = i + 1
if i < 2 then
c.AutoFilter Field:=i, Visibledropdown:=False
End If
Next c

Application.ScreenUpdating = True
End Sub

Then you don't have to worry about what row and what column the
autofilter
range
starts.


CLR wrote:

Here's the whole code.

Sub HideArrows()
'hides all arrows except column 2
Dim c As Range
Dim i As Integer
i = Cells(1, 1).End(xlToRight).Column
Application.ScreenUpdating = False

For Each c In Range(Cells(1, 1), Cells(1, i))
If c.Column < 2 Then
c.AutoFilter Field:=c.Column, _
Visibledropdown:=False
End If
Next

Application.ScreenUpdating = True
End Sub

If I have Autofilter applied and then run this macro, it gets down
to
the
errant part and gives the error message, then when I click "Debug"
it
goes
ahead and hides the arrows...........but leaves me hung up on that
line
of
code.

Vaya con Dios,
Chuck, CABGx3

"Dave Peterson" wrote in message
...
I think it could be lots of things--from worksheet protection to
what
c
is.

Maybe more code and more description about your data--did you
apply
filters
already or what?

CLR wrote:

That's cool Dave, thanks, it basically works, but I have a
little
problem
with it in my XL97

the following returns an error of "1004 Autofilter method of
range
class
failed"

c.AutoFilter Field:=c.Column, _
Visibledropdown:=False

Any idea how to fix?

Vaya con Dios,
Chuck, CABGx3

"Dave Peterson" wrote in message
...
Debra Dalgleish knows how:
http://contextures.com/xlautofilter03.html#Hide

CLR wrote:

Hi All........
Anyone know if it's possible to programmatically turn off
the
DownArrows
on
the display while leaving the Autofilter in it's filtered
state?.....
I would like for my user to see only the Autofiltered
results,
and
not
be
aware that they can reorder them with the downarrows.
Otherwise, can I call my autofilter with code, then freeze
it so
the
downarrows do not work manually until I release them with
more
code.

TIA
Vaya con Dios,
Chuck, CABGx3

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Chuck,
Found another way to do it. Dave's solution of hiding the row is
probably easier, but you can Delete the arrows and retain the filtering.
If you don't have any other shapes on the sheet then this works ...
'--
Sub ss()
Dim shp As Excel.Shape
For Each shp In ActiveSheet.Shapes
shp.Delete
Next
End Sub
'--

'Show the arrows again (no filtering)...
Sub sss()
ActiveSheet.Range("A5").AutoFilter '(specify the top left cell)
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

Thanks Jim........

I've just run out of gas and am heading for the sack........I'll give it a
go tomorrow.

Vaya con Dios,
Chuck, CABGx3


"Jim Cone" wrote in message
...
Chuck,
Found another way to do it. Dave's solution of hiding the row is
probably easier, but you can Delete the arrows and retain the filtering.
If you don't have any other shapes on the sheet then this works ...
'--
Sub ss()
Dim shp As Excel.Shape
For Each shp In ActiveSheet.Shapes
shp.Delete
Next
End Sub
'--

'Show the arrows again (no filtering)...
Sub sss()
ActiveSheet.Range("A5").AutoFilter '(specify the top left cell)
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

Hi Jim......
I appreciate that you keep trying, and came up with this unique method. If
the hiding-row thing were not so easy, I would definately go with this one
of yours over my "put a picture on top" method. The biggest problem is that
I do have several other shapes on that sheet that I would have to do a
different way. So, I guess in the long run, I'll stick with the hidden-row
thing.

Thanks so much for your efforts......they will probably be useful another
day.

Vaya con Dios,
Chuck, CABGx3





"Jim Cone" wrote in message
...
Chuck,
Found another way to do it. Dave's solution of hiding the row is
probably easier, but you can Delete the arrows and retain the filtering.
If you don't have any other shapes on the sheet then this works ...
'--
Sub ss()
Dim shp As Excel.Shape
For Each shp In ActiveSheet.Shapes
shp.Delete
Next
End Sub
'--

'Show the arrows again (no filtering)...
Sub sss()
ActiveSheet.Range("A5").AutoFilter '(specify the top left cell)
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

Agreed, I hate blank rows too...........the bottom line of what I woud up
using is........My header row is row 10, I made and exact copy, headers and
formatting in row 9. Row 9 is hidden most of the time. When I do
Autofilter, it puts the arrows in row 10. When I want to hide them, I just
hide row 10 and unhide row 9..........works cool.

Vaya con Dios,
Chuck, CABGx3


"Dave Peterson" wrote in message
...
I hate inserting blank rows. It just ruins the table structure of the
data (I
think).

But if you really can't have, er, don't want arrows, why not?

CLR wrote:

NOW THATS WHAT I'M TALKING ABOUT!!!!!

A good old-fashioned "cheat".
I was gonna overlay the arrow row with a picture of itself, but this is
a
lot easier.

Thanks Dave, that'll do me.

Vaya con Dios,
Chuck, CABGx3

"Dave Peterson" wrote in message
...
The code wasn't an attempt to get by the arrows showing up (or not
showing
up),
it was just to show you a way not to have to rely on the data being in
A1
through some column in row 1.

Maybe you could add a row under the header row -- keep it empty. Then
apply the
filter to that row and the data below. And hide that empty row with
the
arrows.

CLR wrote:

Thanks Dave, but it does the same thing about killing the Autofilter
when it
kills the arrow on the filtered column..........guess I'll just have
to
cheat <g

Vaya con Dios,
Chuck, CABGx3

"Dave Peterson" wrote in message
...
I don't remember enough about xl97 to know about visibledropdown
not
being
there.

But in xl2k+ you could use this to be more specific about the
range:

Sub HideArrows()
'hides all arrows except column 2
Dim c As Range
Dim i As Long
Application.ScreenUpdating = False

i = 0
For Each c In activesheet.autofilter.range.rows(1).cells
i = i + 1
if i < 2 then
c.AutoFilter Field:=i, Visibledropdown:=False
End If
Next c

Application.ScreenUpdating = True
End Sub

Then you don't have to worry about what row and what column the
autofilter
range
starts.


CLR wrote:

Here's the whole code.

Sub HideArrows()
'hides all arrows except column 2
Dim c As Range
Dim i As Integer
i = Cells(1, 1).End(xlToRight).Column
Application.ScreenUpdating = False

For Each c In Range(Cells(1, 1), Cells(1, i))
If c.Column < 2 Then
c.AutoFilter Field:=c.Column, _
Visibledropdown:=False
End If
Next

Application.ScreenUpdating = True
End Sub

If I have Autofilter applied and then run this macro, it gets
down
to
the
errant part and gives the error message, then when I click
"Debug"
it
goes
ahead and hides the arrows...........but leaves me hung up on
that
line
of
code.

Vaya con Dios,
Chuck, CABGx3

"Dave Peterson" wrote in message
...
I think it could be lots of things--from worksheet protection
to
what
c
is.

Maybe more code and more description about your data--did you
apply
filters
already or what?

CLR wrote:

That's cool Dave, thanks, it basically works, but I have a
little
problem
with it in my XL97

the following returns an error of "1004 Autofilter method of
range
class
failed"

c.AutoFilter Field:=c.Column, _
Visibledropdown:=False

Any idea how to fix?

Vaya con Dios,
Chuck, CABGx3

"Dave Peterson" wrote in message
...
Debra Dalgleish knows how:
http://contextures.com/xlautofilter03.html#Hide

CLR wrote:

Hi All........
Anyone know if it's possible to programmatically turn
off
the
DownArrows
on
the display while leaving the Autofilter in it's
filtered
state?.....
I would like for my user to see only the Autofiltered
results,
and
not
be
aware that they can reorder them with the downarrows.
Otherwise, can I call my autofilter with code, then
freeze
it so
the
downarrows do not work manually until I release them
with
more
code.

TIA
Vaya con Dios,
Chuck, CABGx3

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson