1 | initial version |
Yes, a VBA code can be written to add or delete rows in Excel automatically when there is a change in unique values and data below the formula. The code can be tailored specifically to the requirements of the user. Here is an example code that adds a row when a unique value changes:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Long
Dim CheckRange As Range
Dim i As Long
Set CheckRange = Range("A1:A" & Rows.Count)
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
'Check if the change was in column A
If Not Intersect(Target, CheckRange) Is Nothing Then
'Check for unique values in column A
For i = LastRow To 2 Step -1
If Cells(i, 1).Value <> Cells(i - 1, 1).Value Then
'Insert a row at the change in value
Rows(i).Insert Shift:=xlDown
End If
Next i
End If
End Sub
To delete rows when the data changes, simply replace the Rows(i).Insert Shift:=xlDown
with Rows(i).Delete Shift:=xlUp
.