Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range to VLOOKUP as a Variable (range in another file) | Excel Programming | |||
select range and put range address in variable | Excel Programming | |||
Macro to copy a specified range to a variable range | Excel Programming | |||
setting a range variable equal to the value of a string variable | Excel Programming | |||
Problem trying to us a range variable as an array variable | Excel Programming |