Select column G using on FirstRow and LastRow

Question

I'm having trouble with a line of code. I want to select a column in a datasheet based on the FirstRow and LastRow, I can find the FirstRow and LastRow, I'm using the code below:

Firstrow = Cells(Rows.Count, 1).End(xlUp).End(xlUp).Row
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Rows(Firstrow & ":" & LastRow).Select

When I run this code, it selects the FirstRow and LastRow, but for all the columns. I need to use this to only select column G. I have tried adding this in the code:

Rows(Firstrow & "G:G" & LastRow).Select

But that gives me the error: Type mismatch.

I've also tried to make adjustments in the FirstRow and LastRow statement:

Firstrow = Cells(Rows.Count, 7).End(xlUp).End(xlUp).Row
LastRow = Cells(Rows.Count, 7).End(xlUp).Row

But that doesn't work either.

Anyone experienced with this? Preferably I want to be able to adjust the column in the select function, not the FirstRow and LastRow, because I need to select different columns later on.

`Range(Cells(firstrow, "G"), Cells(lastrow, "G")).Select`. But why are you using `Select`? In general, you should try to avoid it. [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Ron Rosenfeld, Feb 27 '20 at 10:48

braX · Accepted Answer · 2020-02-27T11:59:03.733

2

Use Range instead as you are not trying to select entire rows:

Range("G" & Firstrow & ":G" & LastRow).Select

To use in a SUM formula:

Range("G" & LastRow).Formula = "=SUM(G" & FirstRow & ",G" & LastRowSumC & ")"

edited Feb 27 '20 at 11:59

answered Feb 27 '20 at 10:42

braX

11,506
5
20
33

could you help me with the following. When I want to implement your answer with a sum function, I get the error Expected: end of statement. It selects the first G in the formula. Range("G" & LastRow).Formula = "=SUM("G" & FirstRow & ":G" & LastRow)" – PetePwC Feb 27 '20 at 11:49
thanks for that, cant seem to find any learning material online for this. Could you help me with my next problem: Selection.AutoFill Destination:=Range("P" & FirstRow & ":P" & LastRow). "Autofill method of range class failed – PetePwC Feb 27 '20 at 13:18

score 0 · Answer 2 · answered Feb 27 '20 at 10:52

You could try:

Sub test()

    Dim ColumnNo As Long, FirstRow As Long, LastRow As Long
    Dim ColumnLet As String

    'Create a with statement with the sheet1 you want
    With ThisWorkbook.Worksheets("Sheet1")

        'Import the columne Letter you want
        ColumnLet = "G"
        'Convert column letter to column number
        ColumnNo = Range(ColumnLet & 1).Column

        'Find FirstRow
        FirstRow = .Cells(.Rows.Count, ColumnNo).End(xlUp).End(xlUp).Row
        'Find LastRow
        LastRow = .Cells(.Rows.Count, ColumnNo).End(xlUp).Row

        'Select the range
        .Range(.Cells(FirstRow, ColumnNo), .Cells(LastRow, ColumnNo)).Select

    End With

End Sub

Select column G using on FirstRow and LastRow

2 Answers2