deleted cells

  • Thread starter Thread starter Karen53
  • Start date Start date
K

Karen53

Hi,

Is there a way to catch when cells are deleted from certain columns of a
worksheet?
 
Karen, try the worksheet change event..(Right click the sheet tab>view code)

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Target.Address
End Sub

If this post helps click Yes
 
Hi Jacob,

Thank you for your reply.

In doing this, won't it give me any change? How do I determine cells were
deleted?
 
If you mean delete contents of a cell..

Private Sub Worksheet_Change(ByVal Target As Range)
If IsEmpty(Target) Then MsgBox "Trying to delete contents"
End Sub

If this post helps click Yes
 
Hi Jacob,

No, I mean acutally deleting the cell, not just the contents. Delete >>
Shift cells up etc. Can this be determined?
 
I considered comparing the last used cells to the worksheet on activate to
when it deactivates but this would not really tell me if cells were deleted.
More data could have been input and cells deleted giving me the same last
used cell or more cells. I thought perhaps there was a way to tell if
"Delete" was invoked and at what cell.
 
Hi Karen

There is no delete event as such; however if you want to track a particular
column try the below..

--If you want to track deletion in ColA
--Select a cell out of the data area in ColA..say (cell A100)
--Define a name say 'lastRow' and enter the row number 100 in the cell
--The below will compare the named range value and its current row number.
If the row number is smaller those are deletions ...

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("lastRow").Row < Range("lastRow").Value Then _
MsgBox "Cells deleted from ColA"
End Sub


If this post helps click Yes
 
Thanks Jacob!
--
Thanks for your help.
Karen53


Jacob Skaria said:
Hi Karen

There is no delete event as such; however if you want to track a particular
column try the below..

--If you want to track deletion in ColA
--Select a cell out of the data area in ColA..say (cell A100)
--Define a name say 'lastRow' and enter the row number 100 in the cell
--The below will compare the named range value and its current row number.
If the row number is smaller those are deletions ...

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("lastRow").Row < Range("lastRow").Value Then _
MsgBox "Cells deleted from ColA"
End Sub


If this post helps click Yes
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Copy cells from one worksheet to another workseet - increment row & do again 0
Excel 2013 Merge Data 2
Macro for fast deletion of rows that conatin certain data. 2
VBA set thick borders around non empty cells in range 0
Excel VBA 1
Deleting specific information 3
Updating total cells after column deletions. 1
clear all sheets code 4

Back
Top