Better way to determine the last column

Question

I was hoping that there is a better way to determine the name of the last column in varying ranges.

    Dim iAlpha As Integer, fAlpha As Integer
    Dim iRemainder As Integer, fRemainder As Integer
    Dim ConvertToLetter As String
    Dim fConvertToLetter As String

    iAlpha = Int((DataLength) / 26) '26 for the letters
    fAlpha = Int((DataLength + 2) / 26) 'for the average and sd functions, since they start at C not A
    iRemainder = DataLength - (iAlpha * 26)
    fRemainder = DataLength + 2 - (fAlpha * 26)
    If iAlpha > 0 Then
       ConvertToLetter = Chr(iAlpha + 64)
    End If
    If iRemainder > 0 Then
       ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
    End If
    If fAlpha > 0 Then
       fConvertToLetter = Chr(fAlpha + 64)
    End If
    If fRemainder > 0 Then
       fConvertToLetter = fConvertToLetter & Chr(fRemainder + 64)
    End If

This method works for one example of mine but not for the other. The one it works with has Datalength = 66, which ends at BP because there are two columns before the data starts. The example that doesn't work is Datalength = 120, which ends at CZ.

The question is why do you want the alpha column value? If it is to use in later code you can skip that and use the column index number with the range object `Cells()` — Scott Craner, Aug 24 '17 at 17:56
@ScottCraner Other than to determine whether to execute the if statements, there is no other use for the alphas. — A Cohen, Aug 24 '17 at 17:58
Maybe you should have a look at this https://stackoverflow.com/questions/10106465/excel-column-number-from-column-name for an easy way to convert column indices to alpha column names. Read on, the title is misleading! It handles both ways. — Arminius, Aug 24 '17 at 18:00
can you provide an example if the `if` statement that you feed the alpha column name into. that is what needs to be fixed — jsotola, Aug 24 '17 at 20:38
@jsotola I was able to solve it below. I just need the letter and I do not need the alpha value. — A Cohen, Aug 24 '17 at 20:48

score 0 · Accepted Answer · answered Aug 24 '17 at 18:19

0

Thanks for the guidance. This is what I came up with.

With Worksheets("Reporting")
    Dim lcol As Integer, lcolName As String
    lcol = .Range("A7", .Range("A7").End(xlToRight)).Columns.Count
    lcolName = Split(Cells(, lcol).Address, "$")(1)
End With

answered Aug 24 '17 at 18:19

A Cohen

458
7
26

2

I would still question what you are going to do with `lcolName` if it is to use in a range: `Range(lcolname & "2:" & lcolName & "100")`, this is the slowest method. A simple `Range(Cells(2,lcol),Cells(100,lcol)` is quicker and does not require the extra time to convert. Excel converts the letter column to a number, so you are converting from a number to a letter then Excel converts it back, skip that and save some comp power. – Scott Craner Aug 24 '17 at 18:24
you can also use `lcol=Range("A7").End(xlToRight).Column` to get column count – Karthick Gunasekaran Aug 24 '17 at 18:34
1

@ScottCraner I am using it with range such as this: `Range("C" & (12 * x - 3) & ":" & lcolName & (12 * x - 3))` – A Cohen Aug 24 '17 at 19:17
1

Then `Range(Cells(12*x-3,3),cells(12*x-3,lcol))` is quicker and does not require the parsing and conversion. – Scott Craner Aug 24 '17 at 19:20
`.end(xltoright)).columns.count` would work if there are no spaces – Davesexcel Aug 24 '17 at 19:43

BobtimusPrime · Answer 2 · 2017-08-24T19:45:07.577

I've used this blog post for this question every time I run into it. Avoid using xlRight because if there is a blank column in between, it won't get to the end. Instead, go to the max in the spreadsheet and use xlLeft.

EDIT: There are two snippets of code here, the first to convert a column number to its alpha counter-part up to 676 columns. The second is to find the numeric value of the last column in a dynamic range.

Sub findLetter()

Dim colNum As Long
Dim remainder As Long
Dim firstLetter As String
Dim secondLetter As String
Dim columnLetter As String

colNum = 676 'This is the maximum column number with two letters
secondLetter = ""


If Not colNum > 26 Then
    firstLetter = Chr(colNum)
Else
    remainder = colNum Mod 26
    If Not remainder Then remainder = 26 'Allow for Z

    firstLetter = Chr(WorksheetFunction.RoundDown(colNum / 26, 0) + 64)
    secondLetter = Chr(remainder + 64)

End If

columnLetter = firstLetter & secondLetter

Debug.Print columnLetter

End Sub

This is the code from the aforementioned blog post to just find the number of the last column.

 Sub FindingLastColumn()

'PURPOSE: Different ways to find the last column number of a range
'SOURCE: www.TheSpreadsheetGuru.com

Dim sht As Worksheet
Dim LastColumn As Long

Set sht = ThisWorkbook.Worksheets("Sheet1")

'Ctrl + Shift + End
  LastColumn = sht.Cells(7, sht.Columns.Count).End(xlToLeft).Column

'Using UsedRange
  sht.UsedRange 'Refresh UsedRange
  LastColumn = sht.UsedRange.Columns(sht.UsedRange.Columns.Count).Column

'Using Table Range
  LastColumn = sht.ListObjects("Table1").Range.Columns.Count

'Using Named Range
  LastColumn = sht.Range("MyNamedRange").Columns.Count

'Ctrl + Shift + Right (Range should be first cell in data set)
  LastColumn = sht.Range("A1").CurrentRegion.Columns.Count

End Sub

@ScottCraner I've updated the answer to include both parts of the solution - Find the column number of a dynamic range as well as convert a number to a column letter. — BobtimusPrime, Aug 24 '17 at 19:46

score 0 · Answer 3 · answered Aug 24 '17 at 20:59

0

this snippet returns the alpha column name

Dim cel As Range
Dim colName As String

Set cel = Range("abc123")       ' test cell

colName = Split(Application.Evaluate("=ADDRESS(" & cel.Row & "," & cel.Column & ", 1)"), "$")(1)

Debug.Print colName

answered Aug 24 '17 at 20:59

jsotola

2,238
1
10
22

Better way to determine the last column

3 Answers3