last row query (thats driving me mad!)

  • Thread starter Thread starter jaimetimbrell
  • Start date Start date
J

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.
 
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" <[email protected]>
wrote in message
news:[email protected]...
 
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
 
Thanks for getting back to me on this Nick,

so I place the Dim lLastRow as Long, where all the other Dims are at the top of the macro...

then the lLastRow=Range("R65536").End(xlup).Row, in the body of the code before the equations kick in...

...but then this is where there should also be another Dim defined, Dim Jaime as Dim...(lol)

...I dont know how to put the lLastRow into the equations, it just keeps falling over???

The data is contiguous.

sorry to need so much 'hand holding' on this one!

Regards,
Jaime.
 
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,

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
 
lots of dirrefernt ways but suggest instead of R5C19:R6000C19 us
R5C19.resize(lmaxrow-4)

regard
 
sorry Tony,

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

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.
 
sorry Tony,

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


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.
 
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
 
Brilliant!

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

Regards,
Jaime.
 
Brilliant!

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

Regards,
Jaime.
 
...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!
 
...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!)
 
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.
 
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 said:
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 said:
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?action=getinfo&userid=21074
View this thread:
http://www.excelforum.com/showthread.php?threadid=507462
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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?action=getinfo&userid=21074
View this thread:
http://www.excelforum.com/showthread.php?threadid=507462
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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?action=getinfo&userid=21074
View this thread:
http://www.excelforum.com/showthread.php?threadid=507462
Click to expand...
Click to expand...
Click to expand...
 
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
 
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.


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.
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

macro to copy rows that contain a cell with a certain number 2
Problem with deleting blank rows or last row 4
row argument query 0
row argument query... 5
Trouble selecting multiple sheets using RDB code 11
urgent help needed to clean up code 1
Inaccurate LAST ROW / LAST COLUMN # 3
Fill Down to variable row 1

Back
Top