need a macro

[RSEKAR]

Dear Sir,

A small macro is required. I use excel 2002.
The row has to be selected in which the cell is selected.
In the selection if sutoshapes found that should be deleted.
Thanking you in advance

 

[p45cal]

These autoshapes, do they span across several rows?
What coding have you got so far?

 

[RSEKAR]

I have got a macro which filters and copy data from one column to other. I run the macro every day. After few days I find some of the auto filter buttons (square buttons) are left behind in some of the columns mostly in the 8th row (where the auto filter buttons set up takes place). I rarely find the residual auto filter buttons are found in the 9th and 10th rows. I do not know the cause why these buttons are left behind in some of the columns (could this be due to virus or due to excel 2002). I do not get the residual buttons every time when the macro is executed. Once in a way I find the residual buttons are seen in some the columns and this will stay there until these buttons are deleted manually (I usually delete the row which contains the buttons and insert a new row in the same place. Now the buttons will be deleted). These residual buttons do not have any functions when the buttons are clicked with the mouse pointer. Hence I requested for the macro to delete the auto filter buttons (auto shapes). As I am a beginner in VB I requested you to write the macro. If I find a residual button I will select the cell in that row and make the macro to run. The macro will select the row and delete the buttons.
Thanking you,

 

[p45cal]

Could I see the macros which do things with filters?
I feel there might be something in there that I can adjust. For example, if you never need manually to use these filter buttons, it can be arranged for them never to appear.

 

[RSEKAR]

Thank you very much for your interest in fixing up the problem instead of blindly removing the residual buttons.
Actually my macro starts coping data from (1) book and paste the data in the (2) book and the macro is made to run continuously in the second book calling 5 macros. After my work in the (2) book is over I try to run similar macro which starts copying the data from the (2) book and paste in the (3) book and the macro is made to run continuously in the (3) book calling 5 macros. I get this kind of residual buttons only in the (2) and (3) books. I do not get the buttons in the (1) book. All the macros are working well except leaving the buttons in the (2) and (3) books in the rows 8, 9 and 10. I think I should stop the macro with copying from (1) book and pasting in the (2) book. I should try to run all other calling macros from the (2) book separately. So I try to rearrange the macros and see whether I get the buttons again. If I get the same problem I will send all the macros and explain.
Thanking you once again for your interest shown in this regard.

 

[p45cal]

Just to give you a clue as to the lines I'm thinking along; the Autofilter method has an argument (VisibleDropDown) which defaults to True but can be set to False for each field. If the dropdown arrow shapes are never created, there can never be anything leftover to delete later. Only any good if the Autofilter doesn't need to be adjusted by a user directly on the sheet.

 

[RSEKAR]

I get the buttons again in one book only, previously (I use to get the residual buttons in three books). The following macro deletes the buttons in the 8th row. There are form buttons which are linked with other macros present in other areas of the sheet. If the following macro is made to run the left behind square buttons from the 8th are deleted but at the same time this macro removes other form buttons also from the sheet which should not happen. I will be thankful if you can modify the following macro to act upon the specific range only (A8:AR10) to remove the square buttons only.

Sub Dele_Button()
Dim GetShape As Shape
Dim myRange As String
myRange = "A8:AR10"
Range(myRange).Select

For Each GetShape In ActiveSheet.Shapes
GetShape.Delete
Next

End Sub

 

[p45cal]

Did you try my idea? You haven't posted the code I asked for. You don't tell me what you did to get the the buttons only in one book, not three books.

 

[RSEKAR]

As I said earlier data will be copied from (1) book to (2) book and the macro will continue to work in the (2) book calling 5 macros. Then the data from (2) book will be copied to (3) book and the macro continued to work in the (3) book calling 5 macros. Similar process will take place between (3) book to (4) book. I rearranged the macros. Now the macro will stop in coping from the (1) book to (2) book. Separate macro will start running in the (2) book calling all the macros. Same things are made in all the books. The residual buttons are found in the (3) book only in the 8th row.

Sub COPYTO_DIVAKAR()
Windows("TUMA.xls").Activate
Sheets("ALL").Select
ActiveSheet.AutoFilterMode = False
Range("R10:R949").Select
Selection.Copy
Windows("GDIVAKAR.xls").Activate
Sheets("ALL").Select
ActiveSheet.AutoFilterMode = False
Range("R10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A8").Select
Selection.ClearContents
MsgBox "RATES COPIED TO GDIVAKAR"
End Sub
Sending all the macros for all the 4 books is a big process. Hence I am sending the macro used to copy from the one book to other. Similar coding is used for all the book for coping from one book to other.

 

[p45cal]

You've posted some code which removes autofilters. I can only guess that this is the code that fails to REMOVE some dropdown buttons - at the moment, only you know. I've been suggesting that the arrows are not there to have to remove in the first place (by doing that we don't later have to have a separate routine to rout out those errant buttons and selectively delete them while at the same time not upsetting any other buttons).
In post #6 I talked about the Autofilter method - I still haven't seen such code (do a search for the likes of Autofilter with the Find whole words only box checked - there'll probably be several instances).

All your reiteration of books calling macros is of no value unless I can see the code.
If this code is much too long, then only send snippets including any lines which add or change autofilters to sheets, and perhaps a few lines before so I have a clue what sheet it's working on, and perhaps a few lines after for context. Worst case: place a version of your file(s) on the interweb - box.net, skydrive, whatever.

I have a possible solution probably consisting of adding VisibleDropdown:=False in one or more places (short, simple and robust). You seem to pass over it without saying why.
Is it because the dropdowns need to be manipulated on the sheet by a user? I haven't the foggiest.

We are dealing with Excel misbehaving - you're trying to solve it with sticking plaster, I'm hoping to remove the need for sticking plaster.