Quickie: Shortest syntax to get last column # of named range? 2

[VBAjedi]

Does anyone have a shorter way of getting the column # of the last column in a named range on a worksheet ? The following is what I currently use:

LastCol = Worksheets("Sheet1&quot.Range("MyRange&quot.Cells(1, 1).Column + Worksheets("Sheet1&quot.Range("MyRange&quot.Columns.Count - 1

Thanks!


VBAjedi

 

[Zathras]

It's a kludge, and I wouldn't use it myself, but if you know that the column number will always be between 10 and 99, then you could use

  LastCol=Right(Worksheets("Sheet1").Range("MyRange").Address(ReferenceStyle:=xlR1C1), 2)

 

[DaleWatson123321]

VBAJedi,

Unfortunately Excel doesn't allow that many Worksheet Functions to be used in VBA.

However, here's a fairly simple option, involving the use of a couple of such Worksheet Functions not allowed in VBA - entered instead somewhere in the workbook.

1) Enter this formula in an "out-of-the-way" location:
=CELL("col",MyRange)+COLUMNS(MyRange)-1

2) Assign a range name to the cell containing the formula - something like "colm_frm" (short for column formula).

3) By referencing this worksheet-based formula, your code only needs to be:
LastCol = [colm_frm].Value

Please note that by using the range names, you do NOT have to reference the Worksheet names.

I hope this helps Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[VBslammer]

Function GetColumn(ByRef rng As Range) As Integer
  GetColumn = rng.Columns(rng.Columns.Count).Column
End Function

VBSlammer

Unemployed in Houston, Texas

 

[VBslammer]

I'm surprised by this statement:

"Unfortunately Excel doesn't allow that many Worksheet Functions to be used in VBA"


I use them all the time:

Sub UseWorkSheetFunctionInVBA()
  Dim dblTotal As Double
  dblTotal = WorksheetFunction.Power(3, 10)
  Debug.Print dblTotal
End Sub

This is what the documentation says:

Using Microsoft Excel Worksheet Functions in Visual Basic

You can use most Microsoft Excel worksheet functions in your Visual Basic statements. To see a list of the worksheet functions you can use, see List of Worksheet Functions Available to Visual Basic.

Note Some worksheet functions aren’t useful in Visual Basic. For example, the Concatenate function isn’t needed because in Visual Basic you can use the & operator to join multiple text values.

Calling a Worksheet Function from Visual Basic

In Visual Basic, the Microsoft Excel worksheet functions are available through the WorksheetFunction object.

The following Sub procedure uses the Min worksheet function to determine the smallest value in a range of cells. First, the variable myRange is declared as a Range object, and then it’s set to range A1:C10 on Sheet1. Another variable, answer, is assigned the result of applying the Min function to myRange. Finally, the value of answer is displayed in a message box.

Sub UseFunction()
    Dim myRange As Range
    Set myRange = Worksheets("Sheet1").Range("A1:C10")
    answer = Application.WorksheetFunction.Min(myRange)
    MsgBox answer
End Sub

etc., etc.

VBSlammer

Unemployed in Houston, Texas

 

[xlbo]

This is the shortest way I can come up with:

lastCol = [myRange].Columns([myRange].Columns.Count).Column

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[TonyJollans]

Hi VBAjedi,

Just to throw a spanner in the works on a Friday ...

VBslammer and Geoff both give the same solution which is probably as short as you can get if the range consists of a single area.

When a range has more than one area there is no way of knowing which area has the last (rightmost) column and I think you need to loop through all of them ...

Function LastCol(rngNamed As Range) As Integer

Dim rngArea As Range

LastCol = 1

For Each rngArea In rngNamed.Areas
    If rngArea.Columns(rngArea.Columns.Count).Column > LastCol Then
        LastCol = rngArea.Columns(rngArea.Columns.Count).Column
    End If
Next

End Function

Enjoy,
Tony

 

[VBAjedi]

Zathras - If you wouldn't use it, I wouldn't use it! LOL

Dale - I want to avoid having to set up/use additional worksheet formulas. Some of my apps have a LOT of named ranges, and that would get messy.

Slammer/Geoff - Perfect! Have a star. Just what I was looking for.

Tony - I stay away from ranges that cover multiple areas. Causes too many problems. But I'm sure others reading this thread will find that useful. Thanks!

Thanks everyone for giving this little question more consideration than it probably deserved (we're talking about cutting, what, 5 words out of the code?)!

"Onward and upward!" (extra star for anyone that can tell me what book series that's from. . .)

VBAjedi

 

[DaleWatson123321]

Hi VBAJedi,

"Onward and upward!"... from "Fray" - an 8-issue mini series of comic books. ;-)

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[VBAjedi]

Actually, I was quoting Aslan from "The Chronicles of Narnia". I haven't heard of "Fray" - I wonder if the creator of "Fray" is a C.S. Lewis fan. . .




VBAjedi

 

[VBslammer]

"Onward and Upward" - my former boss after barking about imaginary budget constraints and performance issues so he could ease into the layoff process and still feel good about himself...



VBSlammer

Unemployed in Houston, Texas