Cells instead of range

[bobkap]

I would like to refer to cells with the Columns function but cannot figure out how to do it. Example: Rather than Columns("A:O").Select I'd prefer to use a variable so I can say Columns("A":lastcolumn") or something like that.

Maybe I should be asking how I can use variables with the Columns function?

 

[JoeMo]

Maybe:

Sub LastColumn()
Dim lastCol As Long
lastCol = Cells.Find("*", searchdirection:=xlPrevious).Column
Range("A:" & Replace(Split(Columns(lastCol).Address, "$")(1), ":", "")).Select
End Sub

 

[Peter_SSs]

.. or perhaps this is what you mean?

Dim LastCol As String

LastCol = "P"
Columns("A:" & LastCol).Select

 

[bobkap]

MANY thanks!

 

[frabulator]

You can also use the Cells call instead of Range. The difference is that Cells calls the ROW and then the COLUMN NUMBER instead of the Range style of COLUMN LETTER and then the ROW NUMBER.

Dim str As String
Dim i As Integer
i = 1
str = ActiveSheet.Range("A" & i).Value 'this uses the Range as Column Letter followed by Row number (A1) - or - Columns followed by row (A1:B20)
str = ActiveSheet.Cells(i, i).Value 'this uses Cells as Row Number followed by Column number (1, 1)

 

[Peter_SSs]

MANY thanks!

You're welcome.

 

[Rick Rothstein]

The difference is that Cells calls the ROW and then the COLUMN NUMBER

Just so readers have the complete picture, the Cells object's column argument can be either the COLUMN NUMBER or the COLUMN LETTER (quoted if supplied as a text constant). So either of these would reference the same cell...

Cells(2, 5)

or

Cells(2, "E")

 

[frabulator]

Just so readers have the complete picture, the Cells object's column argument can be either the COLUMN NUMBER or the COLUMN LETTER (quoted if supplied as a text constant). So either of these would reference the same cell...

Cells(2, 5)

or

Cells(2, "E")

I did not realize that.
Many thanks
Learn something new every day!

 

[bobkap]

Thanks. Presumably, you could also use a variable instead of a column number or letter? Is that correct?

 

[frabulator]

As long as it is a predefined variable, yes. Much like the example above with the 'i' being defined as '1'. Example:

Dim str As String
Dim Col as String
Col = "C"
Dim RowN As Integer
RowN = 10
str = ActiveSheet.Range(Col & RowN).Value