use a variable to set a range
 

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.

use a variable to set a range
 

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

"Excel_VBA_Newb" wrote:

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.

use a variable to set a range
 

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" wrote:

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

"Excel_VBA_Newb" wrote:

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.

use a variable to set a range
 

This is dynamic.
SpecialCells(xlCellTypeLastCell)


"Excel_VBA_Newb" wrote:

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" wrote:

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

"Excel_VBA_Newb" wrote:

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.

use a variable to set a range
 

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