PDA

View Full Version : I can never get this simple code working



Djblois
07-20-2006, 09:10 AM
I can never get this line of code to work.

FinalRow = Cells(65536, 1).End(x1up).Row

I declare it publicly at the beginning as

Public FinalRow as Range

What am I doing wrong?

lucas
07-20-2006, 09:17 AM
FinalRow = Cells(A65536, 1).End(x1up).Row


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
07-20-2006, 09:18 AM
Its always something simple

compariniaa
07-20-2006, 09:26 AM
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
07-20-2006, 09:30 AM
oops...forgot to post some code. here are some options:

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

lucas
07-20-2006, 09:39 AM
Sorry, I was in kind of a hurry and I wanted Daniel to read the quote. Maybe this will work:

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

Djblois
07-20-2006, 09:44 AM
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
07-20-2006, 09:51 AM
Option Explicit

Sub x()
Dim FinalRow As Long
FinalRow = Cells(65536, 1).End(xlUp).Row
MsgBox FinalRow
End Sub

lucas
07-20-2006, 09:52 AM
Sub y()
Dim FinalRow As Long
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
MsgBox FinalRow
End Sub

Djblois
07-20-2006, 10:13 AM
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
07-20-2006, 10:18 AM
They both work for me.....see attached
you must have data in col A for this to work

lucas
07-20-2006, 10:20 AM
If you need to check a different column change the 1 to match your column

Djblois
07-20-2006, 10:33 AM
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
07-20-2006, 10:36 AM
It should be xlUp. ie lower case L instead of 1.

lucas
07-20-2006, 10:36 AM
Daniel, its xlUp not x1up it is an L not a 1

lucas
07-20-2006, 10:37 AM
no wonder it wouldn't work. Like I said its usually something small

Djblois
07-20-2006, 10:40 AM
thank you in my book it looks like a 1

Djblois
07-20-2006, 10:46 AM
Now, I am trying to learn my mistakes; This code is also giving me an error:

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

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

austenr
07-20-2006, 10:49 AM
You need an End Select statement at the bottom
For i = 2 To FinalRow



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

Norie
07-20-2006, 01:05 PM
Can you start a new thread?

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