adding to a range in code 4

[HobbitK]

Hi everyone,
I am beginning to think VBA in Excel has a very steep learning curve
I have a practice project that searches for something, selects the matching cell, names it as a range and does some other stuff with that value. Now I need to extend that range by 3 rows. recording a macro did not help, Excel KNOWS the cell references of the activecell and my function does not, and even if I did, there is no guarantee I am smart enough to use that info. So anyone know how to extend my range in code, since it will always change based on the lookup parameter?
Thanks,
Michael

 

[SkipVought]

HobbitK

There are several ways to do what you want to do. Based on the conditions, here are a few suggestions...

1. You are adding rows to a contiguous range of data named MyRange, ie the range was A1:A5 and there are three more rows of data...

   Set MyRange = Range(MyRange, MyRange.End(xlDown))

You can use xlDown, xlUp, xlToRight, or xlLeft

2. Your columns have headings that you want for range names as well as naming the entire table

Sub NameTheRanges()
    Dim sTableName As String
    On Error Resume Next
    sTableName = "MyTableName"
    With ActiveSheet.Cells(1, 1).CurrentRegion
        ActiveWorkbook.Names.Add _
            Name:=sTableName, _
            RefersTo:="=Sheet1!" & .Address
        .CreateNames _
            Top:=True, _
            Left:=False, _
            Bottom:=False, _
            Right:=False
    End With
End Sub

This will name the table (in the current region) and name each column range with the name at the top of the current region.

Hope this helps Skip,
Skip@theofficeexperts.com

 

[RobBroekhuis]

if you always want to extend (not move) the range three rows down, you'd use
MyRange=range(MyRange,MyRange.offset(3,0))
Rob

 

[HobbitK]

Skip ...
I love your code and some day I'm gonna decipher it
But I know my knowledge in VBA is far too limited right now to try to tackle it tonight.

Rob ...
Wonderful, easy, simple .. something I can understand now and build on later. If you help me any more on this, I'm gonna have to pay you.

Thanks Guys
Michael

 

[MatthewGB]

I think both Rob and Skip deserve a star for their efforts on this one!

Matt

 

[HobbitK]

Matt ...
I agree and it has been done.