Insert an empty Row in Excel

  • Thread starter Thread starter Shelby461
  • Start date Start date
S

Shelby461

I would like to know how to write VB code that will insert an empty row
inside a loop. Example:

A B
1 RKG1567 10:40:06
2 TYP7892 10:42:06
3 OPR4567 10:47:35
4 FGB2436 10:52:56
5
6 YTR1234 11:00:55
7 GHU8543 11:12:43
8 BNF2467 11:18: 44

In the above example, there are two columns A & B. My program will loop
through and identify those records that fall within a 30 minute window.
Records 1,2,3 and 4 fall within 10:30:00 through 10:59:59 after which a
blank row is inserted. Any ideas on how to accomplish this? Thanks!
 
Sub BBBBB()
Dim NextTime As Date
Dim res As Variant
Dim rng as Range, rng1 as Range
NextTime = TimeValue("11:00 AM")
Set rng = Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp))
Do
res = Application.Match(CDbl(NextTime), rng, 1)
If Not IsError(res) Then
If rng(res).Value = NextTime Then
Set rng = rng.Offset(-1, 0)
End If
rng(res + 1).EntireRow.Insert
Set rng1 = rng(res + 2)
NextTime = NextTime + TimeValue("00:30")
Set rng = Range(rng1, Cells(Rows.Count, 2).End(xlUp))
If rng(1).Row >= Cells(Rows.Count, 2) _
.End(xlUp).Row Then Exit Sub
Else
Exit Do
End If
Loop

End Sub

worked for me.

--
Regards,
Tom Ogilvy


,
 
As long as you can sort the times in chronological order
You could add 48 lines or as required with nothing other then the time
for each half hour in column B
Regards
Bill K
 
Back
Top