Solved

Excel VBA - Find Range for dbl character columns

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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

624 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