double xlDown

[DonAndress]

Hello.

Today I need to define a range more or less like below:

Worksheets("sheet").Range("M1:Q54")

Now.
Rows 1, 2 and 3 are merged but the rest below is not.
Three first rows contain text and the rest multiple velues (but it's a series).

I don't know if cell Q54 is the last one so I need to use xlDown (Q is the last column).

When I try to use on a keyboard "ctrl+shift+down_arrow" it seems that Excel treats these cells as different type and makes me press the combination twice to select whole range M1:Q54.


How should the code look like so it lets to select different ranges down?

 

[soccerkingpilot]

Take a look at this...just set this equal to a variable and then do Worksheets("sheet").Range("M1:Q" & LastRow)
I'm not entirely sure if it's Range("M1:Q" & LastRow) or Range("M1:Q" LastRow)
http://www.mrexcel.com/td0058.html

Update: I prefer this method listed on the website: LastRow = ActiveSheet.UsedRange.Rows.Count

-Austin

 

[DonAndress]

But how do I define this "UsedRange" part?
The table I'm trying to find the last row of is not the only one in the sheet. Other tables have different amount of rows.

 

[Norie]

If you want to find the last row of data don't use UsedRange, it can give incorrect results - I just tried it with a worksheet with only 7 rows, the row count of the worksheet's UsedRange was 115 - I think that might be a bit out.

There are various other methods, here's a couple:

LastRow = Worksheets("sheet").Range("Q" & Rows.Count).End(xlUp).Row

LastRow = Worksheets("sheet").Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row

 

[soccerkingpilot]

If you want to find the last row of data don't use UsedRange, it can give incorrect results - I just tried it with a worksheet with only 7 rows, the row count of the worksheet's UsedRange was 115 - I think that might be a bit out.

Good to know, thanks for the heads up!