CODEWITHSUNDEEP
excelvbavba7
om prakash
om prakash

Reputation: 29

Dynamically adjust range based on last column in vba

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?

Upvotes: 0

Views: 1258

Answers (2)

pdtcaskey
pdtcaskey

Reputation: 242

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.

Upvotes: 1

BigBen
BigBen

Reputation: 49998

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))

Upvotes: 1

Related Questions