VBA insert multiple rows

[bilsko]

I'd like to insert multiple blank rows into my excel data which is thousands of rows long. The amount of blank rows needed to to be input will be from a value in the cell thats activated.
At the moment i have code that only does 1 row which is shown below but its only a start.
Any help would be appreciated.

Sub InsertBlankRowsBasedOnCellValue()

    Dim Col As Variant
    Dim BlankRows As Long
    Dim LastRow As Long
    Dim R As Long
    Dim StartRow As Long

        Col = "A"
        StartRow = 1
        BlankRows = 1

            LastRow = Cells(Rows.Count, Col).End(xlUp).Row

            Application.ScreenUpdating = False

            With ActiveSheet
For R = LastRow To StartRow + 1 Step -1
If .Cells(R, Col) = "1" Then
.Cells(R + 1, Col).EntireRow.Insert Shift:=xlDown
End If
Next R
End With
Application.ScreenUpdating = True

End Sub

 

[JLGWhiz]

See if this modification does what you want.

Sub InsertBlankRowsBasedOnCellValue()
    Dim Col As Variant
    Dim BlankRows As Long
    Dim LastRow As Long
    Dim R As Long
    Dim StartRow As Long
    Col = "A"
    StartRow = 1   
    LastRow = Cells(Rows.Count, Col).End(xlUp).Row
    Application.ScreenUpdating = False
        With ActiveSheet
            For R = LastRow To StartRow + 1 Step -1
                If .Cells(R, Col) = "1" Then
                    BlankRows = .Cells(R, Col)l.Value
.                   Cells(R + 1, Col).Resize(BlankRows).EntireRow.Insert Shift:=xlDown
                End If
            Next R
        End With
    Application.ScreenUpdating = True
End Sub

It was assumed the

cell thats activated

meant the cell currently addressed by the For loop, since tthe active cells could be anywhere.

 

[bilsko]

Thanks alot.

It didnt quite do what i needed but the tip was in the resize(BlankRows) which did the job for me perfectly.

 

[JLGWhiz]

Thanks alot.

It didnt quite do what i needed but the tip was in the resize(BlankRows) which did the job for me perfectly.

Year, I wasn't quite sure what you had in the cells in column A, but you got the idea and that's what counts.
Regards, JLG