Help finding the last cell in a range.

[wnazzaro]

Same code as yesterday, new problem.

In Excel: I have a group of columns. Row 2 has Q1, Q2, Q3, Q4 repeated for each year that is in row 1, the four cells in row 1 are merged and hold the year. I can find the beginning of this range using the MATCH function (thanks again xld), but not the end of the range, unless I know the last cell of the range.

This is assuming you don't know the last year of the range in row 1 (that would be too easy).

Right now I am setting the range in my MATCH to A2:IT2 to find the last Q4, which is my fix for now, but I would love to know a way to do this (actually, a way to find the end of a row or column instead of a Do Until...Loop = "" or a SendKeys "^{Down}" would really be great).

Also, why does setting my range to A2:IT2 work, but setting it to A2:IU2 doesn't? Setting it to A2:IV2 also doesn't return the last Q4, but that resets my formula in Excel while testing to the range 2:2, which also doesn't work.

So, I figured out to set my range to A2:IT2 while I was writing the post, but knowledge sharing would be helpful. I spent waaaaay too long this morning trying to figure out how to center across a Merge (HorizontalAlignment) and I appreciate the knowledge that can be found here.

Bill

[Bob Phillips]

In Excel: I have a group of columns. Row 2 has Q1, Q2, Q3, Q4 repeated for each year that is in row 1, the four cells in row 1 are merged and hold the year.

Don't use merge cells, they are more trouble than they warrant.

Use Format>Cells>Alignmnet Centre Across Selection instead.

I can find the beginning of this range using the MATCH function (thanks again xld), but not the end of the range, unless I know the last cell of the range.

iLastCol = Cells(2, Columns.Count).End(xlToLeft).Column

Right now I am setting the range in my MATCH to A2:IT2 to find the last Q4, which is my fix for now, but I would love to know a way to do this (actually, a way to find the end of a row or column instead of a Do Until...Loop = "" or a SendKeys "^{Down}" would really be great).

I would just use

Application.MATCH(value,Range("2:2"),0)

that is test the whole row. It is only 256 cells so there is no great problem.

[wnazzaro]

I would just use

Application.MATCH(value,Range("2:2"),0)

that is test the whole row. It is only 256 cells so there is no great problem.

Thanks for the help.

Match("Q4", Range("2:2"), 0) returns the first instance of Q4, not the last. Since Q4 repeats for each year I used Match("Q4", Range("A2:IT2"), 1) which will return the last instance. I still wonder why that works but if you set the range to "A2:IU2" it doesn't work.

Bill

[Bob Phillips]

Thanks for the help.

Match("Q4", Range("2:2"), 0) returns the first instance of Q4, not the last. Since Q4 repeats for each year I used Match("Q4", Range("A2:IT2"), 1) which will return the last instance. I still wonder why that works but if you set the range to "A2:IU2" it doesn't work.

Bill

I don't think I am getting this.

If I have say Q1 in H2, L2, P2, etc., if I do a Application.MATCH("Q1"),Range("2:2"),1) I get the last one fine. So where is the porblem that you have?

Other than that you can use

Application.MATCH("Q1",Range("A2",Cells(2,iLastCol)),1)

as I showed earlier.

[wnazzaro]

I don't think I am getting this.

If I have say Q1 in H2, L2, P2, etc., if I do a Application.MATCH("Q1"),Range("2:2"),1) I get the last one fine. So where is the porblem that you have?

Other than that you can use

Application.MATCH("Q1",Range("A2",Cells(2,iLastCol)),1)

as I showed earlier.

Try this: put Q1 in H2, L2, P2, and T2 and do the Match. I get 16, not 20 like you would expect.

[Bob Phillips]

Dim iYearCol As Long
Dim iQtrOff As Long
Dim iQtrCol As Long
iYearCol = Application.Match(2007, Range("1:1"), 0)
    iQtrOff = Application.Match("Q3", Range(Cells(2, iYearCol), Cells(2, "IV")), 0)
    iQtrCol = iYearCol + iQtrOff - 1
    MsgBox "Cell required is colum " & iQtrCol

[wnazzaro]

Dim iYearCol As Long
Dim iQtrOff As Long
Dim iQtrCol As Long
iYearCol = Application.Match(2007, Range("1:1"), 0)
    iQtrOff = Application.Match("Q3", Range(Cells(2, iYearCol), Cells(2, "IV")), 0)
    iQtrCol = iYearCol + iQtrOff - 1
    MsgBox "Cell required is colum " & iQtrCol

Like I said, it's easy if you know which year is the last year in the range. Gets harder if you don't have that information.

[Bob Phillips]

Like I said, it's easy if you know which year is the last year in the range. Gets harder if you don't have that information.

You're trying me out

Dim iYearCol As Long
Dim iQtrOff As Long
Dim iQtrCol As Long
iYearCol = Cells(1, Columns.Count).End(xlToLeft).Column
    iQtrOff = Application.Match("Q3", Range(Cells(2, iYearCol), Cells(2, "IV")), 0)
    iQtrCol = iYearCol + iQtrOff - 1

[wnazzaro]

You're trying me out

Dim iYearCol As Long
Dim iQtrOff As Long
Dim iQtrCol As Long
 
    iYearCol = Cells(1, Columns.Count).End(xlToLeft).Column
    iQtrOff = Application.Match("Q3", Range(Cells(2, iYearCol), Cells(2, "IV")), 0)
    iQtrCol = iYearCol + iQtrOff - 1

Like I said, it gets harder for me. Apparently not for you. : )
I have so much to learn.