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(R5 C1:RC[-24]),R5C11:R6000C11),2),MIN(IF(R5C19:R6000C19=ROWS(R5 C1: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

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"
wrote in message
news:jaimetimbrell.22kl8m_1138830003.5617@excelfor um-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(R5 C1:RC[-24]),R5C11:R6000C11),2),MIN(IF(R5C19:R6000C19=ROWS(R5 C1: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

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 o
the last used row

regard

--
tony
-----------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...fo&userid=2107
View this thread: http://www.excelforum.com/showthread.php?threadid=50746

Tony,

Thank you also for your very kind reply.

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

Regards,
Jaime

--
jaimetimbrel
-----------------------------------------------------------------------
jaimetimbrell's Profile: http://www.excelforum.com/member.php...fo&userid=2616
View this thread: http://www.excelforum.com/showthread.php?threadid=50746

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

regard

--
tony
-----------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...fo&userid=2107
View this thread: http://www.excelforum.com/showthread.php?threadid=50746

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.


--
jaimetimbrell
------------------------------------------------------------------------
jaimetimbrell's Profile: http://www.excelforum.com/member.php...o&userid=26162
View this thread: http://www.excelforum.com/showthread...hreadid=507462

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

Brilliant!

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

Regards,
Jaime.


--
jaimetimbrell
------------------------------------------------------------------------
jaimetimbrell's Profile: http://www.excelforum.com/member.php...o&userid=26162
View this thread: http://www.excelforum.com/showthread...hreadid=507462

...actually there is one more thing that would help that also applies t
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 paste
them...this is fine and is the only way I could think how to d
it...but i only want to paste down as far as the number of rows in th
"X" column next to the Range("Y5:AB5")...

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

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

Jaime
(LVP!

--
jaimetimbrel
-----------------------------------------------------------------------
jaimetimbrell's Profile: http://www.excelforum.com/member.php...fo&userid=2616
View this thread: http://www.excelforum.com/showthread.php?threadid=50746

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

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" wrote in message
...
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

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" wrote in message
...
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,

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" wrote in message
...
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's answer should be ok as long as all of column A below your star
point is clear. So if it appears not to be working check what is in th
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.

Regard

--
tony
-----------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...fo&userid=2107
View this thread: http://www.excelforum.com/showthread.php?threadid=50746

I use

dim lastRow as integer
Dim lastCol as integer


lastRow =
Application.WorksheetFunction.CountA(ActiveSheet.R ange("a:a")) '
counts all values in column A
lastCol =
Application.WorksheetFunction.CountA(ActiveSheet.R ange("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.



jaimetimbrell Wrote:
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(R5 C1:RC[-24]),R5C11:R6000C11),2),MIN(IF(R5C19:R6000C19=ROWS(R5 C1: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.


--
chubster264
------------------------------------------------------------------------
chubster264's Profile: http://www.excelforum.com/member.php...o&userid=30164
View this thread: http://www.excelforum.com/showthread...hreadid=507462