VB question

[davidhall80]

I would like to find the word "Left" in column A and insert 6 limes beneath it. Thanks in advance

 

[Lewiy]

Try this:

Sub InsertLines()
Dim Limit As Long
Dim c As Long
Limit = Cells(Rows.Count, 1).End(xlUp).Row
For c = 1 To Limit
    If Cells(c, 1) = "Left" Then
        Rows(c + 1 & ":" & c + 6).Insert shift:=xlDown
    End If
Next c
End Sub

 

[davidhall80]

Nice...it worked...cAn you explain this line...specifically the ":" part. Thanks


Rows(c + 1 & ":" & c + 6).Insert shift:=xlDown

 

[Lewiy]

Certainly!

In order to refer to a range of rows, you need to use the “Rows()” function with a string value between the brackets which refers to the rows you want, i.e. if you want to refer to rows 1 to 10 it would look like:

Rows(“1:10”)

Note the quote marks around the reference.
Because of this, it means you can construct a string using variables but you must also still include the colon between the numbers. So, if variable a = 1 and variable b = 10 then you construct the string by saying:
“I want the value of variable a, then the colon, then the value of variable b” or in other words (using an ampersand between parts of the string):

a & “:” & b

So the final reference to rows 1 to 10 would look like:

Rows(a & “:” & b)

Hope that makes sense!

 

[davidhall80]

got it....thanks a million