+OFFSET and COUNTA in VBA

**Baphomay** · 10-29-2013, 08:14 PM

Hi,

I have been using some VBA code to define and name ranges when a workbook is opened, but I've come to a dead end with one of the Dynamic Lists, just wandering if the following line could be converted to "VBA"?
It refers to a Dynamic list called "VendorListDynamic", with sheets called "vendor" and "partslist".

=OFFSET(vendor!$A$2,0,0,COUNTA(partslist!$A:$A),11)

and this is an example of the other code that I am using to name the ranges, this one defines a range and names is "suppliercostslist", using the Sheet "VendorInCosts".

Sub supplierdefine1()
With Workbooks("company.xlsm").Sheets("VendorsInCosts")
Sheets("VendorsInCosts").Activate
.Range("A1").Select
Dim rng As Range
Sheets("VendorsInCosts").Activate
Sheets("VendorsInCosts").Range("A1").Select
Set rng = Range("A1:A" & Sheets("VendorsInCosts").Range("A65535").End(xlUp).Row + 1)
ActiveWorkbook.Names.Add Name:="suppliercostslist", RefersTo:=rng
Application.ScreenUpdating = True
End With
End Sub

thanks for any help

Graham

**GC Excel** · 10-29-2013, 09:34 PM

Hi Baphony
First comment : avoid using .Activate and .Select as it slows down your code. Most of the time you don't need to use it.

Here's an example, if I understood well :

Sub supplierdefine1()
   Dim rng As Range
   With Sheets("VendorsInCosts")
      Set rng = .Range("A1:A" & .Range("A65535").End(xlUp).Row + 1)
      ActiveWorkbook.Names.Add Name:="suppliercostslist", RefersTo:=rng
   End With
   
   'Example :
   Dim rg As Range
   Set rg = Sheets("Vendor").Range("A2").Resize(WorksheetFunction.CountA(Sheets("PartsList").Columns("A:A")), 11)
' Just to verify the ranges address in the Execution pane
   Debug.Print rg.Address
   Debug.Print rng.Address
   
End Sub

**Baphomay** · 10-31-2013, 08:11 PM

Thank you very much GC Excel,

It worked!!!!!!! I'd been trying a couple of weeks to work it out, so thank you and - also want to thank you for the information about the Activate and Select statements, that has saved me extra lines of code

Graham