Select all except last row

Hey everyone,

Probably a stupid question, but can't seem to find the right solution for it on the internet.

I want to select a range, except the last row. I tried it with "offset", but the result is that he moves the selection 1 Up, and that is not what I want. I want to keep the selection, minus the last selected row.

Code I use now is

Worksheets("Sheet1").Activate
Range("A3:B3").Select
Range(Selection, Selection.End(xlDown)).Offset(-1, 0).Select

Thx for the help!

Helo.

Worksheets("Sheet1").Activate
Dim penultimateRow As Long

penultimateRow = Worksheets("Sheet1").Cells(Cells.Rows.Count, 1).End(3).Row - 1

Range("a3:b" & penultimateRow).Select

Thanks for the quick reply, but its a bit more complex (yeah, I should mention it the first time.

I attached what I would like to select (purple) and copy to another file. The starting point of the selection is always the same., but the number of lines can be different. It always ends with the grand total line, but that is the one I don't want to copy...

Thanks!

Hello
Will you copy each of the purple regions to a new worksheet?
Each of the regions for a different worksheet? All for the same spreadsheet but discarding the "grand total" line?
Explain this copy routine, please.

Hello,

- Yes, each purple area should be copied to a other worksheet, where the paste location is fixed. Let say for the first store, they have to paste the values starting in A7, for the second store, paste it in the same workbook/sheet starting in call A2, and so on...
- Answer on your second question, All purple area's should be copied, indeed WITHOUT the grand total line (that's why I tried the offset -1 first) and ALL to 1predefined (so no new, there is a template already) excel.

Open Book1.xlsm
Copy the first purple area
Open Template.xlsx
Paste values in A7
Back to Book1.clsm
Copy second purple area
Back tot Template.xlsx
Paste values in A27
....