Not clearing contents in hidden cells

[The Tamer]

Hiya!

Y'know if you highlight a range of cells that also includes a few hidden columns, then you clear the contents - you clear the contents in the hidden columns (or rows) also... :( Is there any code I can put in the sheet that will only clear the visible cells when the user hits the delete key or right clicks and chooses clear?

:help

Thanks in advance

[Jacob Hilderbrand]

Try this code.


Option Explicit

Sub ChangeDels()
Application.OnKey "{Del}", "NewDel"
Application.CommandBars("Cell").Controls("Clear Contents").OnAction = "NewDel"
End Sub

Sub ResetDels()
Application.OnKey "{Del}"
Application.CommandBars("Cell").Controls("Clear Contents").OnAction = ""
End Sub

Sub NewDel()
Dim DelRange As Range
On Error Resume Next
Set DelRange = Selection.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not DelRange Is Nothing Then
DelRange.ClearContents
End If
End Sub

You can run the code to change the functions on the Workbook Activate event and run the code to reset it on the Workbook Deactivate event.

[The Tamer]

That's cool Jake!

It works great, just wish it wouldn't stop the user's ability to "undo/redo" - but you can't have everything :(

[Jacob Hilderbrand]

Losing Undo is a small price to pay for keeping your data from being deleted.

:beerchug:

[The Tamer]

This is true, very true!

Consider it solved - and thanks again.

[The Tamer]

Oh Darn

This works fine until the user selects just one cell. Then, when he hits delete EVERYTHING that was in any visible cell gets wiped!:wot

[Jacob Hilderbrand]

Try this.

Sub NewDel()

Dim DelRange As Range

If Selection.Cells.Count > 1 Then
On Error Resume Next
Set DelRange = Selection.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
Else
Set DelRange = Selection
End If

If Not DelRange Is Nothing Then
DelRange.ClearContents
End If

End Sub

[The Tamer]

Perfect, thanks matey!! :)

[Jacob Hilderbrand]

You're Welcome :beerchug:

Take Care