Ok, now I have working inserRow Macro.
Now question is how to read hour value from Column_A and make this macro working on them + adding set values (06 or 14 or 22)?
Sub InsertRowsAtValueChange()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
For i = WorkRng.Rows.Count To 2 Step -1
If WorkRng.Cells(i, 1).Value = 2 And WorkRng.Cells(i - 1, 1).Value = 1 Then
WorkRng.Cells(i, 1).EntireRow.Insert
ElseIf WorkRng.Cells(i, 1).Value = 3 And WorkRng.Cells(i - 1, 1).Value = 2 Then
WorkRng.Cells(i, 1).EntireRow.Insert
ElseIf WorkRng.Cells(i, 1).Value = 1 And WorkRng.Cells(i - 1, 1).Value = 3 Then
WorkRng.Cells(i, 1).EntireRow.Insert
End If
Next
Application.ScreenUpdating = True
End Sub
PROGRESS from:
to simplify problem i've made helper column and using google found and changed macro as below.
Sub InsertRowsAtValueChange()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
For i = WorkRng.Rows.Count To 2 Step -1
If WorkRng.Cells(i, 1).Value = 2 And WorkRng.Cells(i - 1, 1).Value = 1 Then
If WorkRng.Cells(i, 1).Value = 3 And WorkRng.Cells(i - 1, 1).Value = 2 Then
If WorkRng.Cells(i, 1).Value = 1 And WorkRng.Cells(i - 1, 1).Value = 3 Then
WorkRng.Cells(i, 1).EntireRow.Insert
End If
WorkRng.Cells(i, 1).EntireRow.Insert
End If
WorkRng.Cells(i, 1).EntireRow.Insert
End If
Next
Application.ScreenUpdating = True
End Sub
But there is some logic error since it puts empty row only in 1st case (change from shift 2 to shift 1) and i cant tell why other wont work. What am I missing?
If it would work is it possible in VBA to read hour value from column A (like in HOUR() Excel function)?
Not to mention i still need to instert set values in those new rows depending on shift...