CODEWITHSUNDEEP
vbaexcelexcel-2010
user3095743
user3095743

Reputation: 21

Use Column Number Instead of A1 in Range?

I have this sub which acts on Column A, A1 down to the last row with data and changes the value in those cells to "text":

Sub Sample()
    Dim lastRow As Long
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("A1:A" & lastRow).Value = "text"
End Sub

Is there a way to use R1C1 notation in a Range instead of A1 notation to define the column?

I hope to eventually use a variable for the column number and step through several columns performing repeated actions then stop at the last column that has data.

Conversely, is it preferable to keep the A1 notation, as in can you point me to a method to step through columns using A1 notation?

I apologize if this is a boneheaded question, I've searched for several days prior to posting. Take pity on a beginner. :) Thank You, Chuck

@Siddarth, Thank you very much, your code is getting me going in the right direction. Using your second Sub I found it changed the Value of all cells to "text". I was interested in manipulating one column at a time and modified the code to only alter Column A, do you see any problems with the syntax?

Sub Sample2()
    Dim ws As Worksheet
    Dim rng As Range
    Dim LastCol As Long, LastRow As Long

    Set ws = ThisWorkbook.Sheets("Results")

    With ws
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

        Set rng = .Range(.Cells(1), .Cells(LastRow, 1))

        Debug.Print rng.Address

        rng.Value = "text"
    End With
End Sub

Subsequently I named a variable which I hope to step and the Sub still works:

Sub Sample2A()
    Dim ws As Worksheet
    Dim rng As Range
    Dim LastCol As Long, LastRow As Long
    Dim StarttCol As Integer

    StarttCol = 1

    Set ws = ThisWorkbook.Sheets("Results")

    With ws
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

        Set rng = .Range(.Cells(StarttCol), .Cells(LastRow, StarttCol))

        Debug.Print rng.Address

        rng.Value = "text"
    End With
End Sub

Upvotes: 2

Views: 5048

Answers (2)

Renan Ranelli
Renan Ranelli

Reputation: 414

Just use cells to specify the range. e.g.: Range(A1) == Range(Cells(1,1))

Something like Range(A1:A10) would be Range(Cells(1,1), Cells(1,10))

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149295

You can use Column Names/Numbers without having to use RC Notation. For example

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range
    Dim LastCol As Long, LastRow As Long
    Dim LastColumn As String

    Set ws = ThisWorkbook.Sheets("Results")

    With ws
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

        '~~> Return column name from number
        LastColumn = Split(.Cells(, LastCol).Address, "$")(1)

        Set rng = .Range("A1:" & LastColumn & LastRow)

        Debug.Print rng.Address

        rng.Value = "text"
    End With
End Sub

The same code can also be written as

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range
    Dim LastCol As Long, LastRow As Long

    Set ws = ThisWorkbook.Sheets("Results")

    With ws
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

        Set rng = .Range(.Cells(1, 1), .Cells(LastRow, LastCol))

        Debug.Print rng.Address

        rng.Value = "text"
    End With
End Sub

Upvotes: 2

Related Questions