How would I write this in vb. If a row or column deleted in sheet1 insert in sheet2 the date it was deleted, the coloumn or row cell name???
Track Deleted Row Or Column
-
-
-
Re: Track Deleted Row Or Column
It's not fool proof, but I've used things like the following code Iincluded in the attachment. Two macros: A Change Detection macro in the Sheet1 private module, and a general macro in a general macro (to write to Sheet2).
Code
Display MorePrivate Sub Worksheet_Change(ByVal Target As Range) Dim What As String If Target.Columns.Count = Columns.Count Then What = "Row " & Target.Row & " Deleted along with " & Target.Rows.Count - 1 & " additonal rows" Call DocumentChange(What) End If If Target.Rows.Count = Rows.Count Then What = "Column " & Target.Column & " Deleted along with " & Target.Columns.Count - 1 & " additonal columns" Call DocumentChange(What) End If End Sub Sub DocumentChange(What As String) Dim r As Long r = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row Sheet2.Cells(r, 1) = What Sheet2.Cells(r, 2) = Now Sheet2.Cells(r, 3) = Environ("username") End Sub
NOTE: If you selct an entire row (or column) and delete the contents of all the cells, this will record the action the same as if the row/column had been itself deleted.
-
Re: Track Deleted Row Or Column
Thanks Ill try it out and let you know how it works.
-
Re: Track Deleted Row Or Column
So if i wanted to use this code for the future would i change where it says sheet2 to what ever the sheet name is such has Sheet3(Deletions) would I use sheet3 or deletion???
-
-
-
Re: Track Deleted Row Or Column
I found one problem. If I insert a row it still says i deleted it. Is there a way it can tell the deffernce between the two???
-
Re: Track Deleted Row Or Column
Regards the sheets naming, I used the Sheet's code name. Sheet2 happened to be "Sheet2) in this case. To use the name you see on the sheet tab the syntax would be.
Regards telling the difference between inserting and deleting a column/row ... that should be doable. Let me think a few minutes as to how I'd do that. (The code as it is now acts if an entire row or column is selected at the time a change is made to the sheet. The "If Target.Columns.Count = Columns.Count Then" code you picked out is the check to see if the current selection -- the Target -- has all of the columns selected.)
-
Re: Track Deleted Row Or Column
The following code may be a bit better. It checks to see if the cells selected after the "deletion" contain any data. So if you delete (remove) a row and the next row down has data in it, then it will record the deletion. If you select a row and delete the cell contents (but don't actually remove the row) ten it will not record a row as having been deleted. Also, if you delete a row or column after the used range, it will not record an action since the newly selected row/column will have no data in any of the cells.
Code
Display MorePrivate Sub Worksheet_Change(ByVal Target As Range) Dim What As String If Target.Columns.Count = Columns.Count And _ WorksheetFunction.CountA(Selection) > 0 Then What = "Row " & Target.Row & " Deleted along with " & Target.Rows.Count - 1 & " additonal rows" Call DocumentChange(What) End If If Target.Rows.Count = Rows.Count And _ WorksheetFunction.CountA(Selection) > 0 Then What = "Column " & Target.Column & " Deleted along with " & Target.Columns.Count - 1 & " additonal columns" Call DocumentChange(What) End If End Sub
-
Re: Track Deleted Row Or Column
Thanks again i will try that out.
-
Re: Track Deleted Row Or Column
Thanks for your help and thanks for explaining that.
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!