What does these two statements mean?

[Phoenix_Turn]

Hi all,

I am a newbie with computing and was wondering wat does these two statements in vba mean?:

intItem = shtProd.Range("F65336").End(xlUp).Row
intRow = shtStatement.Range("A65336").End(xlUp).Row + 1

where the left handside are integeres

and shtProd and shtStatement are worksheets.

Anyone care to explain?

Thanks

 

[VoG]

It is the equivalent of pressing CTRL + Up Arrow.

So this

intItem = shtProd.Range("F65336").End(xlUp).Row

says from the bottom of column F look up till you find a non blank cell. Then .Row gives the row number.

You should actually write

intItem = shtProd.Range("F" & Rows.Count).End(xlUp).Row

as there could be more than 65,536 filled rows in Excel 2007 and later.

 

[Phoenix_Turn]

It is the equivalent of pressing CTRL + Up Arrow.

So this

intItem = shtProd.Range("F65336").End(xlUp).Row

says from the bottom of column F look up till you find a non blank cell. Then .Row gives the row number.

You should actually write

intItem = shtProd.Range("F" & Rows.Count).End(xlUp).Row

as there could be more than 65,536 filled rows in Excel 2007 and later.

Thanks VoG!

So whats with the number? : 65,536? :S

 

[VoG]

65,536 is the maximum number of rows in Excel 2003 and earlier. So it looks up from the bottom possible row to find a filled cell.

using the Rows.Count notation makes this more obvious and is independent of the Excel version.

 

[Phoenix_Turn]

65,536 is the maximum number of rows in Excel 2003 and earlier. So it looks up from the bottom possible row to find a filled cell.

using the Rows.Count notation makes this more obvious and is independent of the Excel version.

Thanks!!!

Btw theres no other way to rewrite/restructure the "sentence" to exclude end(xlup)?

 

[Phoenix_Turn]

oh i forgot to mention,

so what does ("F" & Rows.Count) does exactly?

 

[VoG]

That is the bottommost available row. To see run

Sub Atest()
Range("F" & Rows.Count).Select
End Sub

 

[JackDanIce]

F is your column letter

Rows.Count is determining what the last row number is (i.e. the total number of rows the current sheet has)

F & Rows.Count is providing a cell reference

If you had ("A" & 1) it would be referencing cell A1

 

[Phoenix_Turn]

That is the bottommost available row. To see run

Sub Atest()
Range("F" & Rows.Count).Select
End Sub

oh so it selects the bottom most row which is not empty?

 

[JackDanIce]

Is there a row further down then?

 

[VoG]

No, it selects the absolute bottom row, empty or filled. You can't go any lower than that.