Solved

Excel VBA - Find Range for dbl character columns

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

932 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now