Solved

Excel VBA - Find Range for dbl character columns

Posted on 2013-06-17
3
442 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
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 500 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
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.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

776 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