Column selection to lastrow then resize

Question

I have a code that finds the last row of data in column E and selects the column to that last row. I want to be able to select associated data in columns B through D that goes with column E and then sort based on column B. So I thought I would just find the last row in column E then resize by 3 columns and sort from that selection but I keep getting a run-time error 1004 application-defined or object-defined error. I have provided the code I'm using below. Columns B through D contain data past the end of column E. Thanks!

ws.Range("E1:E" & finalrow).Resize(0, 3).Select

try this: `ws.Range("E1:E" & finalrow).Resize(finalrow, -4).Select`. You need to tell the Resize function how many rows as well. It's not like the `Offset` method. — Scott Holtzman, Mar 04 '16 at 20:26
You rock! I knew I was doing something stupid. Thanks so much :) — Brianna Cates, Mar 04 '16 at 20:27
sorry, I just tested the `-` in the Resize and it failed. You may need to do this: `ws.Range("B1:B" & finalrow).Resize(finalrow, 4).Select` — Scott Holtzman, Mar 04 '16 at 20:28
Yeah I dont think it will let you go negative for some reason — Brianna Cates, Mar 04 '16 at 20:29
offset does, so I was thinking it would follow suit, but nope :( — Scott Holtzman, Mar 04 '16 at 20:29
this code: `ws.Range("E1:E" & finalrow).Offset(, -3).Resize(finalrow, 3).Select` will select cells from `B1` to `D & finalrow` if this is correct result you can use this line: `Range("B1:D" & finalrow).Select` to do that. — Fadi, Mar 04 '16 at 22:29

score 1 · Answer 1 · edited May 23 '17 at 12:31

You may not always be starting in the first row (e.g. E1) so lastRow may not be applicable without some maths. In that case, use With ... End With statements to shorten the code while explicitly referencing the correct cell and cell ranges.

dim lastRow as long
with ws
    lastRow = .cells(.rows.count, "E").end(xlup).row
    'option 1
    .range("B5:D" & lastRow).select

    'option 2
    with .range("E5:E" & lastRow)
        .offset(0, -3).resize(.rows.count, 3).select
    end with

    'option 3
    .range("E5", .cells(lastRow, "G")).offset(0, -3).select
end with

_{See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on Range.Select and Range.Activate to accomplish your goals.}

I tried all 3 of these and I keep getting the same error :/ – Brianna Cates Mar 07 '16 at 15:15 — Brianna Cates, Mar 07 '16 at 15:15

score 0 · Answer 2 · answered Mar 30 '16 at 16:35

0

Something like:

Sub SelectLast3Cols()

Dim ws As Worksheet, lrow As Long

Set ws = Sheets("Sheet3")
lrow = ws.Range("E" & ws.Rows.Count).End(xlUp).Row

    ws.Range("B1", ws.Range("D" & lrow)).Select

End Sub

answered Mar 30 '16 at 16:35

Harley B

543
5
14

Column selection to lastrow then resize

2 Answers2