Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel VBA - Find Range for dbl character columns

Posted on 2013-06-17
3
Medium Priority
?
474 Views
Last Modified: 2013-06-18
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
Comment
Question by:jobprojn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 8

Expert Comment

by:agillanders
ID: 39254995
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
 

Author Comment

by:jobprojn
ID: 39255172
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
 
LVL 6

Accepted Solution

by:
limweizhong earned 2000 total points
ID: 39256122
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

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question