Problem with Range().EntireRow.Insert when creating insertRow sub

[wielandvds]

Hello,


I have a dataset consisting of a one-year PV inverter's input power. The sample time for this dataset isn't constant as it stops logging data in the evening (when depends on the season so it's irregular) and continues again in the morning (also irregular time). I'm trying to create a VBA macro that can insert rows for every 5 minutes between the last point at night and the first point in the morning so I can fill the rows up with zeros to calculate temperature cycle periods. I've created the script below but it gives me an error considering the Range().EntireRow.Insert. Could someone please help me out with this problem?


Sub insertingRows()


Dim counter
Dim hourDiff
Dim minuteDiff
Dim newRows
Dim test
Dim curHour
Dim prevHour
Dim curCell
Dim prevCell


hourDiff = 0
minuteDiff = 0
newRows = 0




Set prevCell = Worksheets("Sheet1").Cells(1, 1)

For counter = 2 To 64822 'Start to compare cell A2 with cell A1

Set curCell = Worksheets("Sheet1").Cells(counter, 1)
curHour = Hour(curCell)
prevHour = Hour(prevCell)

If curHour - prevHour > 1 Then 'If the night passed, add rows untill the next measurement


hourDiff = 24 - Hour(curCell) + Hour(prevCell)
minuteDiff = Minute(prevCell) - Minute(curCell)
newRows = (hourDiff * 12) + Round((minuteDiff / 5), [0]) - 1 'A row is added every 5 mins


Set finalCell = Worksheets("Sheet1").Cells(curCell.Row + newRows, 1)
Range("curCell:finalCell").EntireRow.Insert 'Error, wrong function? Bad range?

End If

prevCell = Worksheets("Sheet1").Cells(counter, 1)

Next

End Sub

[Bob Phillips]

Is this what you want?


Range(curCell, finalCell).EntireRow.Insert

[wielandvds]

Is this what you want?


Range(curCell, finalCell).EntireRow.Insert

That actually did solve my error! The macro is now able to run but still doesn't work properly for some reason and I can't find what's wrong with it .. Troubleshooting line per line now

[Bob Phillips]

In what way does it not work properly? Can you post the workbook and explain what you are trying to do, and what does/doesn't happen?

[wielandvds]

Fixed it myself today. It created an endless amount of empty rows, fixed it with 2 extra conditions:

IsEmpty(curCell) = False And IsEmpty(prevCell) = False

Thanks for helping out with the Range() syntax though :)

Full code for those interested:

Sub insertingRows2()



Dim counter
Dim hourDiff
Dim minuteDiff
Dim newRows
Dim test
Dim curHour
Dim prevHour
Dim curCell
Dim prevCell


hourDiff = 0
minuteDiff = 0
newRows = 0


Set prevCell = Worksheets("Sheet1").Cells(1, 1)


For counter = 2 To 6800 'Start to compare cell A2 with cell A1


Set curCell = Worksheets("Sheet1").Cells(counter, 1)
curHour = Hour(curCell)
prevHour = Hour(prevCell)


If IsEmpty(curCell) = False And IsEmpty(prevCell) = False And Abs(prevHour - curHour) > 2 Then 'If the night passed, add rows untill the next measurement


hourDiff = 24 - Hour(prevCell) + Hour(curCell)
minuteDiff = Minute(curCell) - Minute(prevCell)
newRows = (hourDiff * 12) + Round((minuteDiff / 5), [0]) - 2 'A row is added every 5 mins


Set finalCell = Worksheets("Sheet1").Cells(curCell.Row + newRows, 1)

Range(curCell, finalCell).EntireRow.Insert Shift:=xlDown
MsgBox (hourDiff & "---" & minuteDiff & "---" & newRows & "---" & finalCell)
Else

End If


prevCell = Worksheets("Sheet1").Cells(counter, 1)


Next

End Sub