Code to add entire row based on cell

[Svedde]

Hi everyone,

I’m completely new to the macro functionality and I’ve found a code online that does almost everything that I want for my purpose. I want to add ONE entire blank row below every time something (in my case the letter “G”) appears in a cell within a given range (or in my case, rather within a column). This code does this for me, but since the column to scan always is Column C, I’d like to add that to the code so that I don’t have to set the scan area every time using the code. Can that be done? Here’s the code:

Sub BlankLine()
'Updateby20150203
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type: = 8)
Set WorkRng = WorkRng.Columns(1)
xLastRow = WorkRng.Rows.Count
Application.ScreenUpdating = False
For xRowIndex = xLastRow To 1 Step - 1
Set Rng = WorkRng.Range("A" & xRowIndex)
If Rng.Value = "G" Then
Rng.Offset(1, 0).EntireRow.Insert Shift: = xlDown
End If
Next
Application.ScreenUpdating = True
End Sub

Best Regards / Med vänliga hälsningar,
Henrik

 

[Domski]

Hi,

Try this:

Sub Test()

Dim lngLastRow As Long
Dim lngEachRow As Long


lngLastRow = Range("C" & Rows.Count).End(xlUp).Row


For lngEachRow = lngLastRow To 2 Step -1
    If Range("C" & lngEachRow) = "G" Then
        Rows(lngEachRow + 1).Insert
    End If
Next lngEachRow


End Sub

Dom

 

[Svedde]

Worked like a charm.
Thanks a lot Dom!
/Henrik