Trying to select with .Range( .Cells( #,#) ) won't work in VBA

Question

I'm trying to select a range of cells and I need to use LastRow as the variable that defines the last row of the selection. Here is my code (well the necessary part anyway, it's part of a large loop..):

    Dim LastRow As Long
    LastRow = FlowWkbs(f).Sheets(w).Cells(7,sht.Columns.Count).End(xlToLeft).Column

    With FlowWkbs(f).Sheets(w)
        .Range("G1").FormulaR1C1 = "Normal_0to1"
        .Range("G2").FormulaR1C1 = "0"
        .Cells(LastRow, 7).FormulaR1C1 = "1"
        .Range(.Cells(2, 7), .Cells(LastRow, 7)).Select
        .Cells(LastRow, 7).Activate
    End With

I am getting "Run-time error '1004': Application-defined or object-defined error" as an error on the line:

        .Range(.Cells(2, 7), .Cells(LastRow, 7)).Select

It's funny because it does fine with assigning .Cells(LastRow,7) to a value of 1. But it can't handle the selection of G2:G65335 (that happens to be my last row) In the form that I want it to be in. I need to have the LastRow variable change for each iteration of FlowWkbs(f) and Sheets(w) so I need to use the Range(Cells(#%@%)) method as opposed to Range("G2:G324")

I've looked at a number of other questions/topics on here and they all suggest to add the Workbooks.Worksheets. prefix to the Range and Cells, but the With should take care of all that.

Any ideas?

Answer 1

You're finding the last column and putting the value in LastRow, then using that as the row argument to .Cells. If you need the last row, do something like this:

With FlowWkbs(f).Sheets(w)
    LastRow = .Cells(.Rows.Count, 7).End(xlUp).Row
    .Range("G1").FormulaR1C1 = "Normal_0to1"
    .Range("G2").FormulaR1C1 = "0"
    .Cells(LastRow, 7).FormulaR1C1 = "1"
    .Range(.Cells(2, 7), .Cells(LastRow, 7)).Select
    .Cells(LastRow, 7).Activate
End With

If you need to work with a range of columns, switch the argument order in your calls to .Cells (and rename the misleading variable).

Answer 2

I copy and pasted the wrong variable definition for LastRow, the actual LastRow in my code did find the last row and not column. So the issue wasn't that I was getting the last column. Also, that really wouldn't have answered my question because the variable LastRow would have still had a number value and the same error would've shown up..

The problem was that the Worksheet that I was trying to run the Range.select on wasn't selected first. This is the corrected code (without the row/column typo):

Dim LastRow As Long
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

With FlowWkbs(f).Sheets(w)
    .Range("G1").FormulaR1C1 = "Normal_0to1"
    .Range("G2").FormulaR1C1 = "0"
    .Cells(LastRow, 7).FormulaR1C1 = "1"
    .Select
    .Range(.Cells(2, 7), .Cells(LastRow, 7)).Select
    .Cells(LastRow, 7).Activate
End With

Voila!