inserting rows using visual basic

[dbrewcrew2001]

I have a data set that I have been working on for the past few days.
am new to visual basic and could use a little help with my problem.

The data is organized by time (0,1,2,3...22,23 hrs) but not every hou
is present. I would like to insert any missing hours WITHOU
disrupting the present data. And, any row that is missing an hour, th
rest of the cells in that row need to read "0".

There is an attached file that shows what I have and also what I need.


Thank

Attachment filename: missingrows.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=54731

 

[Frank Kabel]

Hi
for your example try the following macro:

Sub insert_rows()
Dim lastrow As Long
Dim row_index As Long
Dim comp_value
Dim count_rows As Long
Dim fill_index As Long
Dim fill_value

lastrow = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row
For row_index = lastrow - 1 To 4 Step -1
With Cells(row_index, "F")
comp_value = .Value + 1
If comp_value > 24 Then comp_value = comp_value - 24
If .Offset(1, 0).Value <> comp_value Then
count_rows = .Offset(1, 0).Value - comp_value - _
(comp_value > .Offset(1, 0).Value) * 24
.Offset(1, 0).Resize(count_rows, 1).EntireRow.Insert
fill_value = comp_value
For fill_index = row_index + 1 To row_index + count_rows
Cells(fill_index, "F").Value = fill_value
fill_value = fill_value + 1
If fill_value > 24 Then fill_value = 1
Next
.Offset(1, 1).Resize(count_rows, 3).Value = 0
.Offset(1, -5).Resize(count_rows, 5).Value = _
.Offset(0, -5).Resize(1, 5).Value
End If
End With
Next
End Sub