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
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