200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
hello
Thank you for looking at my post
I am wanting to insert 3 rows automatically down a large data set
It is between the 1700 and 1900 hour
Currently I do this manually which is time consuming
In relation to the large data set I am seeking to do this on the fly (instantly)
Attached find a sample
Thank You So Much
hi, please try following vba code for your problem
Sub InsertThreeRowsBetween1700And1900_ColumnPrompt()
Dim ws As Worksheet
Set ws = ActiveSheet
Dim colLetter As String
colLetter = InputBox("Enter the column letter where the time values are (e.g., A, B, C):", "Select Time Column")
If colLetter = "" Then
MsgBox "Operation cancelled."
Exit Sub
End If
Dim colNum As Long
colNum = Columns(colLetter & ":" & colLetter).Column
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, colNum).End(xlUp).Row
Dim i As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
' Loop from bottom to top
For i = lastRow - 1 To 1 Step -1
Dim time1 As Variant, time2 As Variant
time1 = ws.Cells(i, colNum).Value
time2 = ws.Cells(i + 1, colNum).Value
If Format(time1, "hh:mm") = "17:00" And Format(time2, "hh:mm") = "19:00" Then
ws.Rows(i + 1).Resize(3).Insert Shift:=xlDown
End If
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
MsgBox "3 rows inserted between 17:00 and 19:00 in column " & colLetter & "."
End Sub
