part of procedure not executed by button

[Jack Sons]

Hi all,

In the NG I saw earlier questions about residual "down arrows", I mean that
the filter arrows in the top row will not (all) disappear after finishing
the filtering. Saw no usefull answer in the NG. Maybe it occurs when Excel
(I use Excel 2k with XP) gets "overloaded" because the spreadsheet contains
very much formulas, connections to other complex sheets or workbooks with
graphs (or should I say charts?) etc. Perhaps an area of memory gets clotted
with all kinds of scribblings, like with exhausting the memory space for
system resources under Windows 3.11. When reaching its limits the system
behaves incorrect.

I am frequently confronted with "down arrows". I get rid of them with the
sub below. It works perfectly but by the shifting of rows 1 and 2 the
references in another sheet get corrupted and show #VERW! in stead of $A$1
(I think my Dutch version #VERW! would be #REF! in the English version).

Therefore in the last part of the sub a find and replace function is
executed.

I can execute this sub with a button and of course "from within". I mean
that in the VB window there is a menue called Execute (translated from
Dutch, it is the 7th menue from left), the first instruction is execute
sub/userform.

Now the weird thing: executed from the VB window all goes wll, but executed
from the button the last part is not executed, so #VERW! is not changed into
$A$1.

I would like to know the explanation and what to do about it.

Jack Sons
The Netherlands
----------------------------------------------------------------------------
---------------------
Sub DownArrows_weg()
Application.ScreenUpdating = False

If ActiveWorkbook.Name <> "LEERL03.xls" Then

Exit Sub

Else

Sheets("totaal").Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Rows("2:2").Select
Selection.Copy
Rows("1:1").Select
ActiveSheet.Paste
Rows("2:2").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp

Sheets("resultaten en criteria 03").Select
Calculate
Cells.Replace What:="#VERW!", Replacement:="$A$1", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Calculate

Sheets("totaal").Select

Application.ScreenUpdating = True
Calculate
End If


End Sub

 

[BrianB]

Not absolutely certain what you are trying to do. I assume that you ar
removing and replacing a row containing an Autofilter. If this is th
case, it might be worthwhile trying the following code which remove
the autofilter then replaces it.

'-----------------------------
Sub Reset_Autofilter()
If ActiveSheet.AutoFilterMode = True Then
ActiveSheet.Cells.AutoFilter ' remove
ActiveSheet.Cells.AutoFilter ' replace
End If
End Sub
'-------------------------------

I tested your code (using XL97) and found it worked OK. I have
however, revised and simplified it to remove the necessity for use o
Select etc. "#VERW!" is "#REF!" in English. Hope this helps.

'-------------------------------------------------
Sub DownArrows_weg_ver2()
Application.ScreenUpdating = False
If ActiveWorkbook.Name <> "LEERL03.xls" Then
Exit Sub
Else
With Sheets("totaal")
.Rows("1:1").Insert Shift:=xlDown
.Rows("2:2").Copy Destination:=.Range("A1")
.Rows("2:2").Delete Shift:=xlUp
End With
Application.CutCopyMode = False
'----------------------------------------------
Calculate
With Sheets("resultaten en criteria 03").Cells
.Replace _
What:="#VERW!", Replacement:="$A$1", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End With
Calculate
Sheets("totaal").Select
Application.ScreenUpdating = True
End If
End Sub
'---------------------------------------------------

 

[Jack Sons]

Brian,

Thanks for your answer.

Not absolutely certain what you are trying to do. I assume that you are
removing and replacing a row containing an Autofilter.

No, that is not the case. The problem is that under certain circumstances
after an autofilter operation has been concluded in (Sheet("totaal") so
after the auto filter menue item in the data menue has been unchecked) a
number of the column selection arrows in the top row of the sheet will
remain. Don't know how these arrows are called in the English version, I
call them down arrows because they point downwards. Sorry, I was lazy, I
checked in "Excel 2000 Developer's Handbook" by Marion Cottingham, Sybex,
page 667: they are indeed called "down arrows", but in "Excel 2000 Formulas"
by John Walkenbach, M&T, page 236: "drop-down arrows". However, these
remaining down arrows do not function, the are just an eyesore and I want to
get rid of them.

I discovered that the only way to get rid of them ("removing" seems to be
impossible) is by first inserting a blank row below row 1, then copying row
1 to the new row 2, because of which luckily everything is copied but the
down arrows, and then deleting row one. After that row 1 looks exactly like
before the autofilter operation.

In another sheet in that workbook however - I mean Sheet("resultaten en
criteria 03") - all cells with a reference to A1 of Sheet("totaal") got
confused, because of the deleting of the original top row of
Sheet("totaal"), and now show "totaal!#REF!" instead of "totaal!$A$1".

I can repair it by means of find and replace menue item of the edit menue,
all !#REF!'s are replaced by $A$1's and all is well.

I automated things by means of the sub that I showed in my posting. If the
cursor is somewhere within the text of the sub and I use the "execute"
instruction from the menue (I call that "executing from within the code"),
it works as desired. To make things even more simple I assigned that sub
(that macro) to a button I made.

Now the strange thing: when I execute the sub from within teh code
everything goes well. But when I click the button the change all-operation
in Sheet("resultaten en criteria 03") appears not to be executed. All
#REF!'s are still there!

Why the difference between code- and button-operation?
What could possibly be the cause and what should I do to ensure proper
button-execution?

It is very difficult for me to describe things like this in English, the
more abstract the more difficult. I am of course no native speaker, even a
non-speaker. I never have the opportunity to speak English. I just read Time
Magazine and NG stuff. Hope I made myself sufficiently clear this time.

Also hope you will answer.

BTW, very strange that our MVP's and other Excel guru's did not pick up the
very real problem of the residual autofilter down arrows. At first I thought
something as wrong with only my PC, but the postings in the NG (2 postings,
not long after oneother, I think a year or so ago) showed me that I was not
the only one who had that problem. Yet these posting got hardly an answer
and certainly not a good answer, at least not an answer of the quality that
I expected.

Jack.

 

[Dave Peterson]

Just a guess: Is your code in a General module or behind the worksheet?

If it's the worksheet module, then you have an unqualified range reference.

Cells.Replace What:="#VERW!", Replacement:="$A$1", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

In a general module, Cells refers to the activesheet. But behind the worksheet,
this unqualified range refers to the worksheet that owns the code.

Maybe this'll work:

with Sheets("resultaten en criteria 03")
.Calculate
.Cells.Replace What:="#VERW!", Replacement:="$A$1", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
.Calculate
end with

The dot means it refers to the previous With--sheets("result....")


====


I've had the same trouble with disappearing arrows and arrows that stopped
working. Sometimes removing and reapplying the autofilter helps. Sometime
closing and reopening helps. (Sometimes neither helps and I just give up!)

But I'm gonna try your idea--but with a slight change--instead of copy|paste,
how about cut and paste? Then the references should break.

Option Explicit
Option Compare Text
Sub DownArrows_weg()

Dim calcMode As Long

With Application
.ScreenUpdating = False
calcMode = .Calculation
.Calculation = xlCalculationManual
End With

If ActiveWorkbook.Name <> "LEERL03.xls" Then
'do nothing, but continue--to get that last with application stuff
Else
With Worksheets("Totaal")
.Rows(1).Insert
.Rows(2).Cut _
Destination:=.Rows(1)
.Rows(2).Delete
.AutoFilterMode = False
.Range("a1").CurrentRegion.AutoFilter
End With
End If

With Application
.CutCopyMode = False
.Calculation = calcMode
.ScreenUpdating = True
End With

End Sub


Hope it works (for you and me!)

 

[Jack Sons]

Dave,

Thank you for your help.

My code was in a general module, so for the replace operation the active
sheet was indeed "resultaten en criteria 03", as intended.

When I put your code in, the button worked correct although I don't see any
difference between the replace code in my sub and in yours, except for the
with ... end with structure (the cells.Replace part is the same). I can't
fathom this.

Of course the with structure is more efficient because it takes less time,
but this simple sub is finished in a split second. Any other advantage of
the with structuere?

I left out your line .Range("a1").CurrentRegion.AutoFilter
because I don't want to start again an autofilter operation. The remaining
down arrows are an unwanted result of the most recent autofilter operation.
That one was done with, so no need for a repeat, just the need to get rid of
the residue (the remaining down arrows).

You wrote: Sometimes neither helps and I just give up!
I don't understand that. If you want to keep the results of your work (most
likely much more than only a filter operation) you will have to save your
workbook. After opening again the remaning down arrows will still be there.
At least in my case saving and opening again never cleaned my sheet of the
remaining down arrows. So what did you do, close the workbook without
saving, opening anew and starting that piece of work all over?

BTW, I still hope somebody who can explain the (cause of the) misteriously
remaining autofilter down arrows (and how to prevent it) will pick up this
thread and let his light shine on (or at?) this problem. How could this be
brought to the attention of the MVP's?

Jack.

 

[Arien]

Use R1C1 in stead of $A$1.
$ is not supported in macro's

regards,
Arien

 

[Jack Sons]

Arien,

The macro will replace anything, it does not consider $A$1 to be an address,
the Cells.Replace instruction sees it as just "something" that it will have
put in the place where #VERW! is.

Jack.

 

[Dave Peterson]

Interspersed.

Dave,

Thank you for your help.

My code was in a general module, so for the replace operation the active
sheet was indeed "resultaten en criteria 03", as intended.

When I put your code in, the button worked correct although I don't see any
difference between the replace code in my sub and in yours, except for the
with ... end with structure (the cells.Replace part is the same). I can't
fathom this.

I actually had a dot in front of my .Cells.Replace... Reference. That dot means
it belongs to the object in the previous With statement.

(But it didn't help, 'cause I guess incorrectly on where the code resided.)

Of course the with structure is more efficient because it takes less time,
but this simple sub is finished in a split second. Any other advantage of
the with structuere?

I left out your line .Range("a1").CurrentRegion.AutoFilter
because I don't want to start again an autofilter operation. The remaining
down arrows are an unwanted result of the most recent autofilter operation.
That one was done with, so no need for a repeat, just the need to get rid of
the residue (the remaining down arrows).

Actually, I deleted the line that had the autofilter arrows on them. When I
deleted that line, the arrows disappeared. (But when I tested, excel still
thought the worksheet was still filtered.)

So I added ".AutoFilterMode = False" to remove the autofilter completely(?).

But I missed the point of your post. You wanted the arrows gone. So you don't
want that .autofilter at all.

In my case, I've had more problems when the arrows appear, but they don't react
to clicking. Data|Filter|Autofilter is checked and the worksheet is
unprotected, too.

(I guess I added that extra bit for me--not you! Sorry.)

You wrote: Sometimes neither helps and I just give up!
I don't understand that. If you want to keep the results of your work (most
likely much more than only a filter operation) you will have to save your
workbook.

I only meant that I give up on keep the arrows working. I save my work and pass
it on to the person who needed it. (Sometimes the autofilters work for a user
when they don't work for me (and vice versa). I really have no idea why they
stop working or why they come back to life.)

After opening again the remaning down arrows will still be there.
At least in my case saving and opening again never cleaned my sheet of the
remaining down arrows. So what did you do, close the workbook without
saving, opening anew and starting that piece of work all over?

One quick way to remove autofilter from a worksheet manually is to select that
row. Hit the delete key to clear the contents. Then hit ctrl-Z (or Edit|Undo)
to put the values back.

Maybe the arrows will disappear for you. (I wouldn't be money, though.)

BTW, I still hope somebody who can explain the (cause of the) misteriously
remaining autofilter down arrows (and how to prevent it) will pick up this
thread and let his light shine on (or at?) this problem. How could this be
brought to the attention of the MVP's?

There have been posts like this before. I've never seen a real good solution
from anyone. Some suggest that it might be a display problem (my favorite).

If you open the worksheet on another pc, do you still see the same problem?

=======
And one last guess.

If you click Data|filter|Autofilter, do you see a check mark there?

If yes, uncheck it.

I've seen filter arrows on header rows only--the filter didn't include the rows
under it.