last row query (thats driving me mad!)

**jaimetimbrell** · 02-01-2006, 04:38 PM

Hello,

I am new to macro's and I would be grateful for any advice:

I have a macro that looks at about 100 spreadsheets and performs various calculations, below is an example:

Range("Y5").Select
Selection.FormulaArray = _
"=IF(MIN(IF(R5C19:R6000C19=ROWS(R5C1:RC[-24]),R5C11:R6000C11))<SMALL(IF(R5C19:R6000C19=ROWS(R5C1:RC[-24]),R5C11:R6000C11),2),MIN(IF(R5C19:R6000C19=ROWS(R5C1:RC[-24]),R5C11:R6000C11)),"""")"

as you will see there is a constant reference to the last row (R6000) - but I have added this manually as I know this is the last row of the biggest spreadsheet - but there must be a way of calculating which the last row is...but I am a bit dim and cant work out how to programme it...

...I wonder if anyone else could help me with the code?

Thanks in advance.
Jaime.

Nick Hodge · 02-01-2006, 05:20 PM

Jaime

If the data in R is contiguous, I use

Dim lLastRow as Long
lLastRow=Range("R65536").End(xlup).Row

You can then use the number in lLastRow in you range references

(XL97 on)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk

"jaimetimbrell" <jaimetimbrell.22kl8m_1138830003.5617@excelforum-nospam.com>
wrote in message
news:jaimetimbrell.22kl8m_1138830003.5617@excelforum-nospam.com...
>
> Hello,
>
> I am new to macro's and I would be grateful for any advice:
>
> I have a macro that looks at about 100 spreadsheets and performs
> various calculations, below is an example:
>
> Range("Y5").Select
> Selection.FormulaArray = _
> "=IF(MIN(IF(R5C19:R6000C19=ROWS(R5C1:RC[-24]),R5C11:R6000C11))<SMALL(IF(R5C19:R6000C19=ROWS(R5C1:RC[-24]),R5C11:R6000C11),2),MIN(IF(R5C19:R6000C19=ROWS(R5C1:RC[-24]),R5C11:R6000C11)),"""")"
>
> as you will see there is a constant reference to the last row (R6000) -
> but I have added this manually as I know this is the last row of the
> biggest spreadsheet - but there must be a way of calculating which the
> last row is...but I am a bit dim and cant work out how to programme
> it...
>
> ..I wonder if anyone else could help me with the code?
>
> Thanks in advance.
> Jaime.
>
>
> --
> jaimetimbrell
> ------------------------------------------------------------------------
> jaimetimbrell's Profile:
> http://www.excelforum.com/member.php...o&userid=26162
> View this thread: http://www.excelforum.com/showthread...hreadid=507462
>

**tony h** · 02-01-2006, 06:09 PM

Dim rng As Range
Dim lMaxRow As Long

Set rng = ActiveSheet.UsedRange
lMaxRow = rng.Row + rng.Rows().Count - 1

use the actual sheet rather than activesheet. lMAxrow is the number of the last used row

regards

**jaimetimbrell** · 02-02-2006, 06:02 AM

Tony,

Thank you also for your very kind reply.

I have the same question for you as for Nick - how do I then use the variable in the equation to substitute for R6000? (sorry if it is a dumb question!)

Regards,
Jaime.

**tony h** · 02-02-2006, 10:12 AM

lots of dirrefernt ways but suggest instead of R5C19:R6000C19 use R5C19.resize(lmaxrow-4)

regards

**jaimetimbrell** · 02-02-2006, 03:42 PM

sorry Tony,

I have tried the following (and variations) and I am not getting it to work...

In the context of the original equation I have written...

Range("Y5").Select
Selection.FormulaArray = _
"=IF(MIN(IF(R5C19.resize(lmaxrow-4)=ROWS(R5C1:RC[-24]).R5C11resize(lmaxrow-4))...etc

I wonder if you might be good enough to just point me in the right direction...?

Thanks,
Jaime.

**tony h** · 02-02-2006, 05:37 PM

because you are creating a string to put in the formulaarray you need to edit the string rather than use the resize formula I gave you.

so it will be :

Selection.FormulaArray = _
"=IF(MIN(IF(R5C19:R" & cstr(lmaxrows) & "C19=ROWS(R5C1:RC[-24]),R5C11:R" & cstr(lmaxrows) & "C11))<SMALL(IF(R5C19:R" & cstr(lmaxrows) & "C19=ROWS(R5 C1:RC[-24]),R5C11:R" & cstr(lmaxrows) & "C11),2),MIN(IF(R5C19:R" & cstr(lmaxrows) & "C19=ROWS(R5 C1:RC[-24]),R5C11:R" & cstr(lmaxrows) & "C11)),"""")"

I haven't tested it hope it works.

regards

**jaimetimbrell** · 02-03-2006, 06:18 AM

Brilliant!

Thanks Tony, you have really helped me a great deal and I appreciate it.

Regards,
Jaime.

**jaimetimbrell** · 02-03-2006, 09:06 AM

...actually there is one more thing that would help that also applies to the same spreadsheet...

In the macro there is this bit...

Range("Y5:AB5").Select
Selection.Copy
Range("Y6").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.Paste

this copies the formulae down to the last active cell and pastes them...this is fine and is the only way I could think how to do it...but i only want to paste down as far as the number of rows in the "X" column next to the Range("Y5:AB5")...

The practical difference is pasting down only 200 or so rows as opposed to the 3000 rows and therefore a considerable time saving...

...I have tried to look up various solutions but I am struggling...

Jaime
(LVP!)

Lost in Alabama · 02-07-2006, 10:55 AM

Hello, I am Lost in Alabama, and I am lost.

I have a similar issue in that I am trying to find the first cell in Column
A from the bottom up with data, then select from +1 Row to the end of the
sheet which changes in each workbook.

Would you please help? I am very new at this and I greatly appreciate you
experts giving us enlightment.

"tony h" wrote:

>
> because you are creating a string to put in the formulaarray you need to
> edit the string rather than use the resize formula I gave you.
>
> so it will be :
>
>
> Selection.FormulaArray = _
> "=IF(MIN(IF(R5C19:R" & cstr(lmaxrows) &
> "C19=ROWS(R5C1:RC[-24]),R5C11:R" & cstr(lmaxrows) &
> "C11))<SMALL(IF(R5C19:R" & cstr(lmaxrows) & "C19=ROWS(R5
> C1:RC[-24]),R5C11:R" & cstr(lmaxrows) & "C11),2),MIN(IF(R5C19:R" &
> cstr(lmaxrows) & "C19=ROWS(R5 C1:RC[-24]),R5C11:R" & cstr(lmaxrows) &
> "C11)),"""")"
>
>
> I haven't tested it hope it works.
>
> regards
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=507462
>
>

Nick Hodge · 02-07-2006, 03:45 PM

Your not very clear as to whether this is a row (across) you want to select,
or the rest of the column...try:

Sub SelectColumn()
Dim lLastRow As Long
lLastRow = Range("A65536").End(xlUp).Row + 1
Range("A" & lLastRow & ":A65536").Select
End Sub

Sub SelectRow()
Dim lLastRow As Long
lLastRow = Range("A65536").End(xlUp).Row + 1
Rows(lLastRow).EntireRow.Select
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk

"Lost in Alabama" <LostinAlabama@discussions.microsoft.com> wrote in message
news:7A53C9F9-282C-4365-8BA1-EC5180EE5555@microsoft.com...
> Hello, I am Lost in Alabama, and I am lost.
>
> I have a similar issue in that I am trying to find the first cell in
> Column
> A from the bottom up with data, then select from +1 Row to the end of the
> sheet which changes in each workbook.
>
> Would you please help? I am very new at this and I greatly appreciate you
> experts giving us enlightment.
>
>
> "tony h" wrote:
>
>>
>> because you are creating a string to put in the formulaarray you need to
>> edit the string rather than use the resize formula I gave you.
>>
>> so it will be :
>>
>>
>> Selection.FormulaArray = _
>> "=IF(MIN(IF(R5C19:R" & cstr(lmaxrows) &
>> "C19=ROWS(R5C1:RC[-24]),R5C11:R" & cstr(lmaxrows) &
>> "C11))<SMALL(IF(R5C19:R" & cstr(lmaxrows) & "C19=ROWS(R5
>> C1:RC[-24]),R5C11:R" & cstr(lmaxrows) & "C11),2),MIN(IF(R5C19:R" &
>> cstr(lmaxrows) & "C19=ROWS(R5 C1:RC[-24]),R5C11:R" & cstr(lmaxrows) &
>> "C11)),"""")"
>>
>>
>> I haven't tested it hope it works.
>>
>> regards
>>
>>
>> --
>> tony h
>> ------------------------------------------------------------------------
>> tony h's Profile:
>> http://www.excelforum.com/member.php...o&userid=21074
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=507462
>>
>>

Lost in Alabama · 02-08-2006, 08:35 AM

Thank you Nick...I wanted to select all ROWS from -1 Row from the last row of
data found from the bottom up. This should work...I'll test it now.

Thanks for sharing your extensive knowledge with us beginners.

Lost

"Nick Hodge" wrote:

> Your not very clear as to whether this is a row (across) you want to select,
> or the rest of the column...try:
>
> Sub SelectColumn()
> Dim lLastRow As Long
> lLastRow = Range("A65536").End(xlUp).Row + 1
> Range("A" & lLastRow & ":A65536").Select
> End Sub
>
> Sub SelectRow()
> Dim lLastRow As Long
> lLastRow = Range("A65536").End(xlUp).Row + 1
> Rows(lLastRow).EntireRow.Select
> End Sub
>
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> www.nickhodge.co.uk
>
>
> "Lost in Alabama" <LostinAlabama@discussions.microsoft.com> wrote in message
> news:7A53C9F9-282C-4365-8BA1-EC5180EE5555@microsoft.com...
> > Hello, I am Lost in Alabama, and I am lost.
> >
> > I have a similar issue in that I am trying to find the first cell in
> > Column
> > A from the bottom up with data, then select from +1 Row to the end of the
> > sheet which changes in each workbook.
> >
> > Would you please help? I am very new at this and I greatly appreciate you
> > experts giving us enlightment.
> >
> >
> > "tony h" wrote:
> >
> >>
> >> because you are creating a string to put in the formulaarray you need to
> >> edit the string rather than use the resize formula I gave you.
> >>
> >> so it will be :
> >>
> >>
> >> Selection.FormulaArray = _
> >> "=IF(MIN(IF(R5C19:R" & cstr(lmaxrows) &
> >> "C19=ROWS(R5C1:RC[-24]),R5C11:R" & cstr(lmaxrows) &
> >> "C11))<SMALL(IF(R5C19:R" & cstr(lmaxrows) & "C19=ROWS(R5
> >> C1:RC[-24]),R5C11:R" & cstr(lmaxrows) & "C11),2),MIN(IF(R5C19:R" &
> >> cstr(lmaxrows) & "C19=ROWS(R5 C1:RC[-24]),R5C11:R" & cstr(lmaxrows) &
> >> "C11)),"""")"
> >>
> >>
> >> I haven't tested it hope it works.
> >>
> >> regards
> >>
> >>
> >> --
> >> tony h
> >> ------------------------------------------------------------------------
> >> tony h's Profile:
> >> http://www.excelforum.com/member.php...o&userid=21074
> >> View this thread:
> >> http://www.excelforum.com/showthread...hreadid=507462
> >>
> >>
>
>
>

Lost in Alabama · 02-08-2006, 08:50 AM

Nick,

I tried the SelectRows Sub and it selects the row below the last row of
data, but not all rows from there down to 65536. I think it is because the
rows below that point have data in some of the cells. I need it to key off
of Column A where all cells are blank after the last row of data.

Thanks Again for your help.

Lost

"Nick Hodge" wrote:

> Your not very clear as to whether this is a row (across) you want to select,
> or the rest of the column...try:
>
> Sub SelectColumn()
> Dim lLastRow As Long
> lLastRow = Range("A65536").End(xlUp).Row + 1
> Range("A" & lLastRow & ":A65536").Select
> End Sub
>
> Sub SelectRow()
> Dim lLastRow As Long
> lLastRow = Range("A65536").End(xlUp).Row + 1
> Rows(lLastRow).EntireRow.Select
> End Sub
>
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> www.nickhodge.co.uk
>
>
> "Lost in Alabama" <LostinAlabama@discussions.microsoft.com> wrote in message
> news:7A53C9F9-282C-4365-8BA1-EC5180EE5555@microsoft.com...
> > Hello, I am Lost in Alabama, and I am lost.
> >
> > I have a similar issue in that I am trying to find the first cell in
> > Column
> > A from the bottom up with data, then select from +1 Row to the end of the
> > sheet which changes in each workbook.
> >
> > Would you please help? I am very new at this and I greatly appreciate you
> > experts giving us enlightment.
> >
> >
> > "tony h" wrote:
> >
> >>
> >> because you are creating a string to put in the formulaarray you need to
> >> edit the string rather than use the resize formula I gave you.
> >>
> >> so it will be :
> >>
> >>
> >> Selection.FormulaArray = _
> >> "=IF(MIN(IF(R5C19:R" & cstr(lmaxrows) &
> >> "C19=ROWS(R5C1:RC[-24]),R5C11:R" & cstr(lmaxrows) &
> >> "C11))<SMALL(IF(R5C19:R" & cstr(lmaxrows) & "C19=ROWS(R5
> >> C1:RC[-24]),R5C11:R" & cstr(lmaxrows) & "C11),2),MIN(IF(R5C19:R" &
> >> cstr(lmaxrows) & "C19=ROWS(R5 C1:RC[-24]),R5C11:R" & cstr(lmaxrows) &
> >> "C11)),"""")"
> >>
> >>
> >> I haven't tested it hope it works.
> >>
> >> regards
> >>
> >>
> >> --
> >> tony h
> >> ------------------------------------------------------------------------
> >> tony h's Profile:
> >> http://www.excelforum.com/member.php...o&userid=21074
> >> View this thread:
> >> http://www.excelforum.com/showthread...hreadid=507462
> >>
> >>
>
>
>

**tony h** · 02-08-2006, 05:08 PM

Nick's answer should be ok as long as all of column A below your start point is clear. So if it appears not to be working check what is in the cell - remember a space is a someyhing.

Remember the reverse route would also work (if it suits your data) namely modify Nick's code to:
lLastRow = Range("A1").End(xldown).Row + 1

This code equates pressing <ctrl><down arrow> on the spreadsheet.

Regards

**chubster264** · 02-08-2006, 05:20 PM

I use

dim lastRow as integer
Dim lastCol as integer

lastRow = Application.WorksheetFunction.CountA(ActiveSheet.Range("a:a")) ' counts all values in column A
lastCol = Application.WorksheetFunction.CountA(ActiveSheet.Range("1:1")) ' counts all values in row 1

Won't work if there are gaps in your data, but I don't think any of the other methods would either.

Originally Posted by jaimetimbrell

Hello,

I am new to macro's and I would be grateful for any advice:

I have a macro that looks at about 100 spreadsheets and performs various calculations, below is an example:

Range("Y5").Select
Selection.FormulaArray = _
"=IF(MIN(IF(R5C19:R6000C19=ROWS(R5C1:RC[-24]),R5C11:R6000C11))<SMALL(IF(R5C19:R6000C19=ROWS(R5C1:RC[-24]),R5C11:R6000C11),2),MIN(IF(R5C19:R6000C19=ROWS(R5C1:RC[-24]),R5C11:R6000C11)),"""")"

as you will see there is a constant reference to the last row (R6000) - but I have added this manually as I know this is the last row of the biggest spreadsheet - but there must be a way of calculating which the last row is...but I am a bit dim and cant work out how to programme it...

...I wonder if anyone else could help me with the code?

Thanks in advance.
Jaime.