Delete cels with selection

[davefish]

Hi, I ma trying to delete a bunch of cells on a hidden worksheet, but the only code I can get to work has a .select suffix that causes the worksheets to be viewed. Is ther anyway I can get around this using the code or similar below:-

Sub Clear_BOM()
'
' Clear_BOM Macro

Application.EnableCancelKey = xlDisabled
'
With ActiveWorkbook.Sheets("BOMM").Activate
'
Range("A20:F39").Select
Selection.ClearContents
Range("A4:A16").Select
Selection.ClearContents

End With


Cheers Davefish

 

[SkipVought]

hi,

Sub Clear_BOM()
'
' Clear_BOM Macro

    Application.EnableCancelKey = xlDisabled
'
    With ActiveWorkbook.Sheets("BOMM")
'
       .Range("A20:F39").ClearContents
       .Range("A4:A16").ClearContents
    
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[davefish]

Hi Skip,

Worked beautifully!

Thank you very much

davefish

 

[UnsolvedCoding]

A precaution I take is never to assume that only one workbook will be open at a time. Because of that I always specify what workbook is to be used.

If the activworkbook is the same as the workbook you are writting code from you can write it using thisworkbook. The advantage is you don't get screwed up if more than one workbook is open.

So for Skips example of deleting multiple ranges it would read

With Thisworkbook.Sheets("BOMM")
.Range("A20:F39").ClearContents
.Range("A4:A16").ClearContents
End With

If you only need to delete one range this will work -

Thisworkbook.Sheets("BOMM").Range("A20:F39").ClearContents

 

[UnsolvedCoding]

Or you can do something more complex and do this

Thisworkbook.Sheets("BOMM").Range("A20:F39, A4:A16").ClearContents

 

[SkipVought]

I seldom use Active anything: workbook, worksheet, cell - NADA!

If you have multiple workbooks open, you ought to explicitly reference the appropriate workbook.

ThisWorkbook may NOT be the appropriate workbook.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[davefish]

Skip,

I use Active, select etc almost all the time. As an expert user you say, no to that, so what's the best way to explicitly select a cell in a sheet without using any of the above.

Interest of a casual user !

Davefish

 

[SkipVought]

The real question is, do you want the cell or range selected, so that the user will see that selection once the procedure is finished, OR do you want something DONE on that range, that does not really need to be selected, but merely REFERENCED?

Selecting stuff, reallys slows down a procedure and is almost always unnecessary.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}