VBA dynamic variable value

Question

There is no dumb questions, only dumb people asking questions. I might fall in that category right now.

I want to achieve the following :

Sub test
Dim lastcolumn as long
lastcolumn = Range("A1").End(xlToRight).Column
DebugPrint lastcolumn '=5
'Add 2 columns between first and last col
DebugPrint lastcolumn '=7
End Sub

right now I need to do as follow :

Sub test
Dim lastcolumn as long
lastcolumn = Range("A1").End(xlToRight).Column
DebugPrint lastcolumn '=5
'Add 2 columns between first and last col
lastcolumn = Range("A1").End(xlToRight).Column
DebugPrint lastcolumn '=7
End Sub

So I end up with

lastcolumn = Range("A1").End(xlToRight).Column

all over the place

What's the correct way to do that?

unclear where you want to add the column. Inbetween the first and last column is not useful unless you only have 2 columns. If you have 10 columns, where do you want the new columns added? — urdearboy
The better way to find the last column is lastcolumn = Cells(1, Columns.Count).End(xlToLeft).Column — urdearboy
lastColumn stores the column number but is not updated when the sheet changes... — BigBen
I'm sorry my question is not clear. it doesn't really matter where I insert the columns as long as it's between the first and the last. What I'm trying to do is for the "lastcolumn" to always "refresh" when I use it without having to always retype it. And I fully agree with your second comment. — remyfra

remyfra remyfra · Accepted Answer · 2020-05-28T20:23:21

What about something like this ? Is it bad ? Might not be performant

'Option Explicit

Public Enum DynamicVar
    lastcolumn
    lastrow
End Enum

Public Function MyVar(name As DynamicVar) As Variant

If name = lastcolumn Then
    MyVar = Cells(1, Columns.Count).End(xlToLeft).Column
    Exit Function

ElseIf name = lastrow Then
    MyVar = Cells(Rows.Count, 1).End(xlUp).Row
    Exit Function

End If

End Function

Sub test()
Cells(MyVar(lastrow), MyVar(lastcolumn)).value = "Wathever"
End Sub'

Edit : Maybe this is a bit better

Public Function MyVar(name As DynamicVar) As Variant

Select Case name

    Case lastcolumn
        MyVar = Cells(1, Columns.Count).End(xlToLeft).Column

    Case lastrow
        MyVar = Cells(Rows.Count, 1).End(xlUp).Row

End Select

End Function

VBA dynamic variable value

1 Answers