• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 531
  • Last Modified:

Excel VBA - Find Range for dbl character columns

Hello Experts.  I have a piece of VBA code that looks at a named range of a table within Excel and returns the address range.  This works fine until we hit columns that are double characters (i.e. AA, AB, AC).  

I'm not a vba expert and would love some assistance in getting this piece of code to work for named ranges that span dual character column range letters.  Any help is appreciated.  Excel version is 2007, 2010, 2013.

   Private Function fnListFillRange(ByRef pstrRangeName As String) As Boolean
   Dim MyTable As Excel.Range
   Dim lngRow(1 To 2) As Long
   Dim lngCol(1 To 2) As Long
   Dim strRange As String
   
   On Error GoTo 0
   
   Set MyTable = Worksheets("Lookups").Range(pstrRangeName)
   
   lngRow(1) = MyTable.ListObject.HeaderRowRange.Row + 1
   lngRow(2) = lngRow(1) + MyTable.ListObject.ListRows.Count - 1
   
   lngCol(1) = MyTable.ListObject.HeaderRowRange.Column
   lngCol(2) = lngCol(1) + MyTable.ListObject.ListColumns.Count - 1
   
   strRange = MyTable.Worksheet.Name & "!$" & Chr(lngCol(1) + 64) & "$" & lngRow(1) & ":$" & Chr(lngCol(2) + 64) & "$" & lngRow(2)
   
   MsgBox (strRange)
   
End Function

Open in new window


Thanks.
0
jobprojn
Asked:
jobprojn
1 Solution
 
agillandersCommented:
Use strRange =  MyTable.Address in place of line 17.

You can ditch lines 11-17 and therefore lines 3 & 4 also.

It does all the conversion for you. Look at the Address function in more detail and you'll find it supports a variety of options for specifying addresses.

Alistair
0
 
jobprojnAuthor Commented:
Thanks Alistair.  One thing our code does is figures out the range of the data and ignores the table headers.  This is a requirement for our code which I failed to mention in my original post.  It's for this reason that we can't simply use MyTable.Address.

If we do as you suggest I'm fairly certain we will end up with the header row, correct?
0
 
limweizhongCommented:
Well, you could just use this:
Private Function fnListFillRange$(ByRef pstrRangeName$)
    fnListFillRange = Worksheets("Lookups").Range(pstrRangeName).ListObject.DataBodyRange.Address
End Function

Open in new window

Moreover, if you had the name of the list itself, you could even reduce it to:
Private Function fnListFillRange$(ByRef pstrRangeName$)
    fnListFillRange = Worksheets("Lookups").ListObjects(pstrRangeName).DataBodyRange.Address
End Function

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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