Excel VBA if row or column is inserted or deleted

Problem description:

Since I am addicted to Excel and VBA I was developing a database with advanced features using Excel and VBA. The idea was to save data in the form of three dimensional form and since Excel is working with 2D tables, the solution was using different sheets as 2D layers to form the 3D data storage. In this database there is one sheet visible which is basic layer and the other sheets are hidden. 

One of the challenges I faced is: when I insert or delete row in the basic sheet, the relation between this sheet and the other sheets is corrupted because of data shifting. For this reason, the solution is to automatically detect the insert or delete of row(s) and column(s) in the basic sheet and apply this change in the other sheets. The following schematic shows the concept of 3D data storage in Excel.

How row or column insertion or deletion works?

When row is inserted, the rows below are shifted down. When new column is inserted, the columns on the right (for left-to-right sheets) are shifted right. When row or column is inserted, the new inserted row or column is highlighted (as if it is selected)

How the algorithm works?

The algorithm is massive improvement for an algorithm found in this link http://stackoverflow.com/questions/35978015/determining-when-a-row-cell-is-inserted-or-deleted-in-excel-vba

This algorithm depends mainly on what is called "Cell ID" or "Cell identity" which is a hidden non-user-accessible piece of data (string type) that can be set programmatically for any cell. The cell ID follow the following rules:

[1] Cell ID is not copied when cell is copied

[2] Cell ID is cleared when cell content is cleared

[3] Cell ID is cut or moved when cell is cut or moved to another place

Detecting column insert is more easier than column delete

This algorithm has the following advantages:

❶ Works fine when one of this ranges is selected:

- Single cell
- Single entire row
- Multiple entire rows
- Single entire column
- Multiple entire columns
- Vector row
- Vector column
- Last row
- Last column

❷ It returns the following data:

- Number of rows inserted
- Number of columns inserted
- Rows inserted before ....
- Columns inserted before ....

❸ It does not conflict with defined names

❹ It does not conflict with formulas

❺ It can not be fooled by clearing contents or range cut. From one side, pressing "Delete" on certain range will clear contents and IDs as well and this could be interpreted as range delete. From the other side, cell ID is cleared on range cut. Both of these bugs are solved.

❻ It supports different insert and delete patterns:

Successive insert -  Successive delete - Insert then delete - Delete then insert
❼ Compatibility with:

Excel 2003, 2007,2010, 2013, and 2016

❽ It works well either "Insert" or "Delete" commands are called from popup menu or the ribbon

❾  Cell IDs are temporary data and not saved on workbook save or close

Here you are the code to be place in the sheet code:

Dim PreviousSelection As String   Private Sub Worksheet_Change(ByVal Target As Range)   ' If entire column(s) is(are) selected If Target.Rows.Count = ActiveSheet.Rows.Count Then If Target.Cells(1, 1).Address = Cells(Target.Row, Target.Column + Target.Columns.Count).ID Then    ' Case of inserting entire column Debug.Print CStr(Target.Columns.Count) + " columns inserted before column " + CStr(Target.Column) ElseIf Target.Cells(1, 1).ID = "" And Target.Item(1, 1).Address = PreviousSelection And Cells(Rows.Count, Columns.Count).ID <> Cells(Rows.Count, Columns.Count).Address Then ' Case of deleting entire column Debug.Print "Columns deleted from column " + CStr(Target.Column) + " to column " + CStr(Target.Column + Target.Columns.Count - 1) End If End If   ' If entire row(s) is(are) selected If Target.Columns.Count = ActiveSheet.Columns.Count Then If Target.Cells(1, 1).Address = Cells(Target.Row + Target.Rows.Count, Target.Column).ID Then     ' Case of inserting entire row Debug.Print CStr(Target.Rows.Count) + " rows inserted before row " + CStr(Target.Row) ElseIf Target.Cells(1, 1).ID = "" And Target.Item(1, 1).Address = PreviousSelection And Cells(Rows.Count, Columns.Count).ID <> Cells(Rows.Count, Columns.Count).Address Then     ' Case of deleting entire row Debug.Print "Rows deleted from column " + CStr(Target.Row) + " to column " + CStr(Target.Row + Target.Rows.Count - 1) End If End If   ' Emulate IDs resetting as in range selection (at the end of Worksheet_Change sub-routine)   Cells(Rows.Count, Columns.Count).ID = Cells(Rows.Count, Columns.Count).Address   Target.Cells(1, 1).ID = Target.Cells(1, 1).Address   On Error Resume Next Cells(Target.Row, Target.Column + Target.Columns.Count).ID = ""   On Error Resume Next Cells(Target.Row + Target.Rows.Count, Target.Column).ID = ""   End Sub   Private Sub Worksheet_SelectionChange(ByVal Target As Range)   Cells(Rows.Count, Columns.Count).ID = Cells(Rows.Count, Columns.Count).Address     'To help not mis-recognize clear cell contents   PreviousSelection = Target.Item(1, 1).Address    'Useful in case of range cut   ' A routine for clearing all IDs is needed or clear ID of the previously selected range Target.Cells(1, 1).ID = Target.Cells(1, 1).Address   On Error Resume Next Cells(Target.Row, Target.Column + Target.Columns.Count).ID = ""   On Error Resume Next Cells(Target.Row + Target.Rows.Count, Target.Column).ID = ""   End Sub