use a variable to set a range

[Excel_VBA_Newb]

How do I perform this:

'Obtain last column that contains data
lastCol = ActiveSheet.UsedRange.Column - 1 +
ActiveSheet.UsedRange.Columns.Count

'Convert to Alpha
(code omitted for brievity)

'Define the Column in the range based on the previous check
Range(lastCol:lastCol).Insert Shift:=xlToLeft

Thanks.

 

[Joel]

With ActiveSheet
Set LastCell = .Cells.SpecialCells(xlCellTypeLastCell)
LastCell.EntireColumn.Insert Shift:=xlToLeft
End With

 

[Jim Thomlinson]

Note that the used range and last cell is not foolproof. There will be times
when lastcell will return a range well beyond the last poulated cell. If you
wna tthe true last cell then you need to use a function something like this...

Public Function LastCell(Optional ByVal wks As Worksheet, _
Optional ByVal blnConstantsOnly As Boolean = False)
As Range
Dim lngLastRow As Long
Dim lngLastColumn As Long
Dim lngLookIn As Long

If blnConstantsOnly = True Then
lngLookIn = xlValues
Else
lngLookIn = xlFormulas
End If

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
LookIn:=lngLookIn, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
lngLastColumn = wks.Cells.Find(What:="*", _
LookIn:=lngLookIn, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
lngLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, lngLastColumn)

End Function
'***************************************
Which you can use in your code like this...

public sub InsertLastColumn()
lastcell(activesheet).offset(0, -1).entirecolumn.insert
end sub

 

[Excel_VBA_Newb]

Thanks, Joel. However, I don't think I was very clear in my needs.

The problem I'm running into, is the column that i need to load into the
Range is dynamic. Sometime's it might be column C other times it might be
column G, etc.

So, I need to know how to load the range based on a variable. Let's throw
the insert out of the equation (I think that is confusing things). I need to
check for the last column then load a range based on last column - 1.

So, how do I load a range based on the variable.

Range("C:C") will give me the entire column "C"
How can I select a range based on the variable: Range(variable:variable)

Thanks!

 

[Joel]

This is dynamic.
SpecialCells(xlCellTypeLastCell)

 

[norie]

There's no need to convert to the letter.

If you have the column number you could just use it.

ColNum = 3 ' column 3 is column C

Columns(ColNum).Interior.Color = vbRed