Thursday, December 29, 2016

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