CODEWITHSUNDEEP
excelvba
GCC
GCC

Reputation: 295

How to select the last cell in a column range?

I am trying to select the final value of a range and put that value into the cell Q4. The range is dynamic and start in the fourth row.

Here is my code:

Sub test()
    Dim PSpark As Worksheet
    Dim lc As Long

    Set PSpark = Worksheets("ws1")
    lc = PSpark.Cells(4, Columns.Count).End(xlToLeft).Column

    With PSpark            
        .Range("Q4") = lc.Value
    End With
End Sub

Any help would be greatly appreciated.

Upvotes: 2

Views: 79

Answers (1)

Wizhi
Wizhi

Reputation: 6549

Very close, I think you want this:

So we use the column number you found and then pick the value in that last column, for row 4. We copy the value to cell Q4. Cells(row, column)

Sub test()

Dim PSpark As Worksheet
Dim lc As Long

Set PSpark = Worksheets("ws1")
lc = PSpark.Cells(4, Columns.Count).End(xlToLeft).Column 'Gives us the last column number. If the value is in Column C, then we get lc = 3.

    With PSpark

        .Range("Q4") = .Cells(4, lc).Value 'Which row from column lc (which is the last column) we want to copy from

    End With


End Sub

Upvotes: 1

Related Questions