Get the column after last

[agibsonsw]

Suppose I have a table occupying A1:H20. I can use Range("A1").CurrentRegion to get the area, then CreateNames to create defined names based on the column headings. How can I (neatly) get a reference to the column after last? That is, to I2:I20? And then to the cell I1?

There are a number of ways I can do this. I count count the columns and rows, and use this information and Range(x, y) to manually build the reference, but I feel there should be a neater way to just, for example, get the last column-range H2:H20 and just offset this by 1?

[Rudi]

Here are a few options...

Code: Select all

'Method 1 (Column I1:I20)
    Range("A1").CurrentRegion.Columns.Offset(0, Range("A1").CurrentRegion.Columns.Count).Columns(1).Select
'Method 2 (Column I1:I20)
    Range("A1").CurrentRegion.Columns(Range("A1").CurrentRegion.Columns.Count).Offset(0, 1).Select


'Method 1 (Cell I1)
    Range("A1").CurrentRegion.Columns.Offset(0, Range("A1").CurrentRegion.Columns.Count).Cells(1).Select
'Method 2 (Cell I1)
    Range("A1").CurrentRegion.Cells(Range("A1").CurrentRegion.Columns.Count).Offset(0, 1).Select
'Method 3 (Cell I1)
    Cells(Range("A1").CurrentRegion.Columns.Count).Offset(0, 1).Select
'Method 4 (Cell I1)
    Range("A1").End(xlToRight).Offset(0, 1).Select
'Method 5 (Cell I1)
    Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).Select

[agibsonsw]

Thank you @Rudi, not quite the Van Gogh I was hoping for

Actually, I realised that getting I1 is easy once I have I2:I20 because I can just do Range("I2:I20").Offset(-1,0).Cells(1,1).. although there are probably a few more versions. Andy

[HansV]

Rudi gave you I1:I20 instead of I2:I20. Here is some code to get I2:I20:

Code: Select all

    Dim rngColumn As Range
    Dim rngCell As Range

    With Range("A1").CurrentRegion
        Set rngColumn = .Resize(RowSize:=.Rows.Count - 1, ColumnSize:=1) _
            .Offset(RowOffset:=1, ColumnOffset:=.Columns.Count)
        Set rngCell = .Cells(1, .Columns.Count + 1)
    End With

    ' To see the result:
    Debug.Print rngColumn.Address, rngCell.Address

[agibsonsw]

Thanks Hans.

I've continued to play. I can select I1:I20 in yet more ways (I've named A1:H20 "data"):

Code: Select all

Intersect(range("data"), range("data").SpecialCells(xlCellTypeLastCell).EntireColumn).Offset(,1).Select

Range("data").Columns(Range("data").Columns.Count).Offset(,1).Select

(Using keyboard shortcuts I can select the adjacent column easily but it doesn't translate to something useful when recorded.)

I was then trying to reduce the column by the top cell.. without using Resize. It's fun to explore but I think I'm looking for something that isn't there :)