Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • Last Modified:

vba add range name on demand

Dear Experts,

I have an issue whereby I am trying to specify a range name on the fly but I cannot seem to get it to work the way I want.

To make this clear, I do not want to use formula's or anything to do with CurrentRegion as my range name will commence from row 2 as the worksheet has headers.

Enclosed is my existing code:

 With shtFrom.Range("tbl_import")

'collect last row
                lastrow = shtFrom.Cells(2).CurrentRegion.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
                
                'Range("A2 " & L & lastrow).Select
                csb_import = Range("A2:L & lastrow)
                ActiveWorkbook.Names.Add Name:="csb_import", RefersTo:=csb_import
         
                'allocate from range
                Set rngFrom = Range("csb_import")

Open in new window


Any ideas to help appreciated guys?
0
discogs
Asked:
discogs
  • 3
  • 2
1 Solution
 
aikimarkCommented:
assuming that csb_import is a range, all you lacked was a SET keyword:
With shtFrom.Range("tbl_import")

'collect last row
                lastrow = shtFrom.Cells(2).CurrentRegion.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
                
                'Range("A2 " & L & lastrow).Select
                Set csb_import = Range("A2:L & lastrow)
                ActiveWorkbook.Names.Add Name:="csb_import", RefersTo:=csb_import
         
                'allocate from range
                Set rngFrom = Range("csb_import")

Open in new window

0
 
aikimarkCommented:
You might simplify your code as follows, unless you need to use the csb_import range.
With shtFrom.Range("tbl_import")

'collect last row
                lastrow = shtFrom.Cells(2).CurrentRegion.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
                
                ActiveWorkbook.Names.Add Name:="csb_import", RefersTo:=Range("A2:L & lastrow)
         
                'allocate from range
                Set rngFrom = Range("csb_import")

Open in new window

0
 
discogsAuthor Commented:
Hi Mark.

Thanks for the help.

Just to clarify, the line:

ActiveWorkbook.Names.Add Name:="csb_import", RefersTo:=Range("A2:L & lastrow)

Open in new window


I get an error. Should it not be:


Open in new window

ActiveWorkbook.Names.Add Name:="csb_import", RefersTo:=Range("A2:L & lastrow")

TA
0
 
aikimarkCommented:
misplaced quote marks
ActiveWorkbook.Names.Add Name:="csb_import", RefersTo:=Range("A2:L" & lastrow)

Open in new window

0
 
discogsAuthor Commented:
Legend! Thanks heaps champ.
TA
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now