0

I have a table in excel where i am inserting some column, because of that last column is getting changed, I have to detect the last column and set it as a range in vba

I have written code to find the last column and convert it to alphabet, however i am not able to set it as a range.

Public Function ColumnLetter(ColumnNumber As Long) As String
ColumnLetter = Split(Cells(1, ColumnNumber).Address(True, False), "$")(0)
End Function

Dim Rng As Range
Dim LastColumn, LastRow As Long
Dim Str As String

LastRow = Cells(Rows.Count, 2).End(xlUp).Row
LastColumn = Cells(2, Columns.Count).End(xlToLeft).Column
Str = ColumnLetter(LastColumn)
'Set Rng = Range("M7:M" & LastRow)

I want to write Commented line as

Set Rng = Range( & Str & "7" & LastRow)

To make it dynamic How can i do this?

BigBen
  • 46,229
  • 7
  • 24
  • 40
om prakash
  • 29
  • 1
  • 7

2 Answers2

1

First, change Str to something else; you're shadowing the Str function.

Dim colLetter As String
colLetter = ColumnLetter(LastColumn)

Then concatenate as necessary:

Set Rng = Range(colLetter & "7:" & colLetter & LastRow)

Note that you can skip using the column letter and just use Cells with the column index.

Set Rng = Range(Cells(7, LastColumn), Cells(LastRow, LastColumn))
BigBen
  • 46,229
  • 7
  • 24
  • 40
1

As others pointed out, Str isn't the best variable name. Let's say you change it to "ColLtr" ... then your specific answer is Range(ColLtr & "7:" & ColLtr & LastRow)

However, might I suggest another tack?

If you are trying to set the range as the lower right corner of the used range, use:

With {sheet}.UsedRange
Set Rng = .Cells(.Rows.Count,.Columns.Count)
End With

(substitute {sheet} with WorkSheets("MySheet") or ActiveSheet or however you prefer to specify the worksheet.)

If you need the entire column, adjust it to:

With {sheet}.UsedRange
Set Rng = .Columns(.Columns.Count)
End With

BUT ... if you "Format Range as a Table" (creating a ListObject), you can bypass VBA altogether and use [TableName[ColumnHeader]], either in VBA or in your worksheet formulae. Of course, "TableName" and "ColumnHeader" would be specific to your table's name and the actual text in your column header.

pdtcaskey
  • 242
  • 1
  • 9
  • A side note: `UsedRange` is [unreliable](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) when attempting to find the last row/column of data. – BigBen May 26 '21 at 14:26
  • Thanks for pointing that out. I tend to still use it and make sure unused columns and rows are fully deleted, so i don't think a whole lot about that quirk (and since I typically write for very limited audiences, too). – pdtcaskey May 26 '21 at 15:21