Not clearing contents in hidden cells

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

Try this code.

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.

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 :(

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

:beerchug:

This is true, very true!

Consider it solved - and thanks again.

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

Try this.
[vba]
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
[/vba]

Perfect, thanks matey!! :)

You're Welcome :beerchug:

Take Care