How can I edit this code to work only a selection of rows/cells and not the whole worksheet

Viewed 33

I was wondering if there is a way to run a code on a selection of cells/rows instead of the whole worksheet. The code I've used is a code I've got online for inserting a blank row based on the cell value. So all i did was substitute the value and the column in the original code to suit my requirements. But now the issue is it keeps inserting rows in the whole sheet every time i run the code. Is there a way i can alter this code to only work on a selection of rows/cells?

The problem is that I’d like to be able to run a Macro on a selection, that is, I’d like to select the cells and have the macro run on ONLY those cells. Then I could select the cells, run the macro, make another selection, run the macro, etc.

The code I've used is:

Sub InsertBlankRowsBasedOnCellValue()
    Dim Col As Variant
    Dim BlankRows As Long
    Dim LastRow As Long
    Dim R As Long
    Dim StartRow As Long

    Col = "K"
    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) = "RO" Then
                .Cells(R + 1, Col).EntireRow.Insert Shift:=xlDown
            End If
        Next R
    End With

    Application.ScreenUpdating = True
End Sub

I am not a coder. I occasionally google excel hacks to create more user friendly working papers. Hope someone can help.

1 Answers

Just set your variables as below:

StartRow = Selection.Row
LastRow = Selection.Rows.Count + Selection.Row - 1
Related