I can never get this simple code working

[Djblois]

I can never get this line of code to work.

[VBA]FinalRow = Cells(65536, 1).End(x1up).Row[/VBA]

I declare it publicly at the beginning as

[VBA]Public FinalRow as Range[/VBA]

What am I doing wrong?

[lucas]

[vba]

FinalRow = Cells(A65536, 1).End(x1up).Row
[/vba]

Lastrow = ActSh.Cells(Rows.Count, 1).End(xlUp).Row
Explain each part of this code please. I have a general idea of what it is doing, selecting the last row of code w/ data and selecting moving up to avoid missing data in non-contiguous cells.

Answer:from Jake
Lastrow is a variable that will hold this value.
ActSH is an object that was (presumably) set to a certain worksheet. Like this:
Set ActSh = Sheets("PCCombined_FF")
Cells is a way to refer to a specific cell on the worksheet by refering to the cell row, then the cell column. In this case, the row will be 65536 (since that is the current count of rows for an Excel worksheet), and 1 referd to column A. So this is the cell A65536.
End(xlUp) will move the reference up. Assuming that A65536 is blank, then it will move up, skipping all blank cells and come to the first non-blank cell encountered.
.Row will return the row of the cell we get after the End(xlUp).
Basically what this does is get the last row used in a certain column.

[lucas]

Its always something simple

[compariniaa]

i think you may want to double-check that....he doesn't need the "A" in front of 65536, because he's declaring the column with the 1 in the cells statement

the problem is how he's declaring the variable. if he wants a range variable, he needs to drop the ".row"
if he wants a long variable, he needs to change "as Range" to "as Long"

[compariniaa]

oops...forgot to post some code. here are some options:

[VBA]Public FinalRow as Range
'then, instead of FinalRow=.....
set FinalRow=cells(65536,1).end(xlup)

'or if you just want the row number
Public FinalRow as Long
FinalRow=cells(65536,1).end(xlup).row[/VBA]

[lucas]

Sorry, I was in kind of a hurry and I wanted Daniel to read the quote. Maybe this will work:
[VBA]
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
[/VBA]

[Djblois]

lol I just want the row number

So I declared it As long like you said and I am still getting an error message saying variable not defined and it highlights x1up

[lucas]

Option Explicit
[VBA]
Sub x()
Dim FinalRow As Long
FinalRow = Cells(65536, 1).End(xlUp).Row
MsgBox FinalRow
End Sub

[/VBA]

[lucas]

[VBA]Sub y()
Dim FinalRow As Long
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
MsgBox FinalRow
End Sub[/VBA]

[Djblois]

Lucas I tried both of them and neither work and I have option explicit at the top. Also what does Option explicit mean and why do I need it? I am sorry

[lucas]

They both work for me.....see attached
you must have data in col A for this to work

[lucas]

If you need to check a different column change the 1 to match your column

[Djblois]

I know I need the column number to be correct. I do have info in the 1st column and that is what I am using. However, it gives me an error saying variable not defined and it hightlights x1up

[Norie]

It should be xlUp. ie lower case L instead of 1.

[lucas]

Daniel, its xlUp not x1up it is an L not a 1

[lucas]

no wonder it wouldn't work. Like I said its usually something small

[Djblois]

thank you in my book it looks like a 1

[Djblois]

Now, I am trying to learn my mistakes; This code is also giving me an error:

[VBA]For i = 2 To FinalRow
Select Case Cells(i, 1).Value
Case "Customer"
CustomerTab
Case "Product:"
ProductTab1
Case "ShipTo"
ShipTo2
Next i[/VBA]

It is saying next without for. Witch makes no sense since I can see the For right there.

[austenr]

You need an End Select statement at the bottom[vba]

For i = 2 To FinalRow

Select Case Cells(i, 1).Value
Case "Customer"
CustomerTab
Case "Product:"
ProductTab1
Case "ShipTo"
ShipTo2
End Select
Next i

[/vba]

[Norie]

Can you start a new thread?

By the way do you have Intellisense turned on? (Tools>Option>Auto List Members)