how to delete cells without shifting and rows/columns

[Guest]

Hi,
I've tried to use "clearcontents" & "clear" but neither works the way I
wanted (I was using a 3rd party add-in, the add-in is disabled unless
"delete" is used. The down side is that would shift all my controls, too
(putting them all into the upper left corner). However, if I manually select
the whole worksheet, and press the delete key, that seems to work exactly
what I want (which is to clear all the contents, and yet leaving all the
controls where there are without shifting).

Please help
Jeff

 

[Tom Ogilvy]

Sounds like you need to try using SendKeys.

because if you record this action it uses ClearContents - and you say that
doesn't work.

 

[Guest]

Thanks for trying, it still doesn't work.

Here is my code:
Worksheets("Main").Select
Worksheets("Main").Cells.Select
SendKeys "{DEL}", True

as the codes finished, once again the contents are cleared, but the
add-in button is still gray out. One thing I noticed is when I hit the
delete key manually, a msgbox pops up w/ the msg (that doesn't happen when I
use the sendkeys, clear, or clearcontents):
"The range you deleted is associated with a query that retrieves data
from an external source. do you want to delete the query in addition to the
range? if you click no, ..."


Thanks
Jeff

 

[Tom Ogilvy]

I did a Get External Data to the Northwind.mdb and put that in sheet Main.

then went to another sheet and ran this:

Sub AAAAA()
Worksheets("Main").Select
Worksheets("Main").Cells.Select
SendKeys "{DEL}", False
End Sub

I got the prompt you describe.

I didn't test it with True, so try changing to False. And have it as the
last command in your macro.

 

[Guest]

Thanks Tom,
It only works with "false"
Here is another problem I have, when I tried to select cell("B1"), I
don't get the prompt.
Worksheets("Main").Select
Worksheets("Main").Cells.Select
SendKeys "{DEL}", False
Worksheets("Main").Range("B1").Select

Everything works fine if I comment out the last line. Is it a way to
put a delay (so I can click yes before set focus to B1?)

Thanks a lot for your help

 

[Tom Ogilvy]

Two things you can try
Sub Test1()
Worksheets("Main").Select
Worksheets("Main").Cells.Select
SendKeys "{DEL}", False
DoEvents
Worksheets("Main").Range("B1").Select
End Sub

or

Sub Test1()
Worksheets("Main").Select
Worksheets("Main").Cells.Select
Application.OnTime now + TimeValue("00:00:02"),"Test2"
SendKeys "{DEL}", False
End sub

Sub Test2
Worksheets("Main").Range("B1").Select
End Sub

I haven't tested either. The appearance of the dialog may affect
performance - do you really want the dialog to appear.

 

[Tom Ogilvy]

Screwed up the Ontime command. Should be

Application.OnTime now + TimeValue("00:00:02"),"Test2",,True

 

[Guest]

Tom,
DoEvents doesn't work.
It doesn't matter if the dialog box pops up or not. If all possible I'd
rather pass in "yes" so the dialog box doesn't show up at all.

Thanks

 

[Tom Ogilvy]

Sub Test1()
Application.DisplayAlerts = False
Worksheets("Main").Select
Worksheets("Main").Cells.Select
Application.OnTime now + TimeValue("00:00:02"),"Test2",,True
SendKeys "{DEL}", False
End sub

Sub Test2
Worksheets("Main").Range("B1").Select
Application.displayAlerts = True
End Sub

Should suppress the popup and answer Yes.

 

[Guest]

the message box still appears.

Sub Test1()
Application.DisplayAlerts = False
Worksheets("Main").Select
Worksheets("Main").Cells.Select
Application.OnTime now + TimeValue("00:00:02"),"Test2",,True
SendKeys "{DEL}", False
End sub

Sub Test2
Worksheets("Main").Range("B1").Select
Application.displayAlerts = True
End Sub

Should suppress the popup and answer Yes.

 

[Tom Ogilvy]

This worked for me.

Sub AAAATest1()
Application.DisplayAlerts = False
Worksheets("Main").Select
Worksheets("Main").Cells.Select
Application.OnTime Now + TimeValue("00:00:01"), "Test2", , True
SendKeys "{DEL}~", False
End Sub
Sub Test2()
Worksheets("Main").Range("B1").Select
Application.DisplayAlerts = True
End Sub

 

[Guest]

Thanks alot for your help.
Yes, it does work, i left off the "~" on sendkeys so it didn't emulate
pressing enter.

 

[Tom Ogilvy]

hmmm
the point of the enter is to drop the dialog without user intervention -
otherwise we are back to 4 or 5 posts ago. But whatever.