VBA - Select columns using numbers

Question

I m looking for an alternative to this code  but using numbers  I want to select 5 columns  the start column is a variable  and then it selects 5 columns from this   Columns  A E   Select   How do I use integers instead  to reference columns  Something like below   For n   1 to 5 Columns  n   n   4    select do sth next n   Thanks  Gemmo

User · Answer

no need for loops or such   try this     dim startColumnas integer  dim endColumn as integer  startColumn   7  endColumn   24  Range Cells   startColumn   Cells   endColumn   ColumnWidth   3 8    lt    whatever width you want to set

User · Answer

You can specify addresses as  R1C2  instead of  B2   Under File -  Options -  Formuals -  Workingg with Formulas there is a toggle R1C1 reference style  which can be set  as illustrated below

User · Answer

Columns  A E   Select   Can be directly replaced by  Columns 1  Resize   5  EntireColumn Select   Where 1 can be replaced by a variable  n   5 Columns n  Resize   n 4  EntireColumn Select   In my opinion you are best dealing with a block of columns rather than looping through columns n to n   4 as it is more efficient   In addition  using select will slow your code down  So instead of selecting your columns and then performing an action on the selection try instead to perform the action directly  Below is an example to change the colour of columns A-E to yellow   Columns 1  Resize   5  EntireColumn Interior Color   65535

User · Answer

In this way  you can start to select data even behind column  Z  and select a lot of columns   Sub SelectColumNums       Dim xCol1 As Integer  xNumOfCols as integer     xCol1   26     xNumOfCols   17     Range Columns xCol1   Columns xCol1   xNumOfCols   Select End Sub

User · Answer

You can use resize like this   For n   1 To 5     Columns n  Resize   5  Select         gt  rest of your code Next   In any Range Manipulation that you do  always keep at the back of your mind Resize and Offset property

User · Answer

Try using the following  where n is your variable and x is your offset  4 in this case    LEFT ADDRESS 1 n x 4  1    This will return the letter of that column  so for n 1 and x 4  it ll return A 4   E   You can then use INDIRECT   to reference this  as so   COLUMNS INDIRECT LEFT ADDRESS 1 n 4  1  amp     amp LEFT ADDRESS 1 n x 4  1      which with n 1  x 4 becomes   COLUMNS INDIRECT  A  amp     amp  E      and so   COLUMNS A E

User · Answer

In the example code below I use variables just to show how the command could be used for other situations   FirstCol   1 LastCol   FirstCol   5 Range Columns FirstCol   Columns LastCol   Select

User · Answer

you can use range with cells to get the effect you want  but it would be better not to use select if you don t have to   For n   1 to 5 range cells 1 n  entirecolumn cells 1 n 4  entirecolumn  Select do sth next n

User · Answer

I was looking for a similar thing  My problem was to find the last column based on row 5 and then select 3 columns before including the last column    Dim lColumn As Long  lColumn   ActiveSheet Cells 5 Columns Count  End xlToLeft  Column MsgBox   The last used column is     amp  lColumn  Range Columns lColumn - 3   Columns lColumn   Select   Message box is optional as it is more of a control check  If you want to select the columns after the last column then you simply reverse the range selection  Dim lColumn As Long  lColumn   ActiveSheet Cells 5 Columns Count  End xlToLeft  Column MsgBox   The last used column is     amp  lColumn  Range Columns lColumn   Columns lColumn   3   Select

[vba] VBA - Select columns using numbers?

Examples related to vba

Examples related to excel