questions about Dim
 

Hi

I want to make the lastrow of sheet1 column A common knowledge between
my macros. Reason being is i want to refer the the lastrow a lot of
times

I thought maybe public sub would help this is my code

Public figs
' Count Rows in table
Dim lastRowpub As Long
lastRowpub = Worksheets("Sheet1").Cells(Rows.Count,
"A").End(xlUp).Row


It keeps coming up saying

Compile Error: Invalid Outside Procedure

and then highlights the (xlUp) part of the line


any ideas?


--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile: http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=516165

questions about Dim
 

You need a sub

Public figs
Public lastRowpub As Long

Sub CountRows()
' Count Rows in table
lastRowpub = Worksheets("Sheet1").Cells(Rows.Count,"A").End(xlU p).Row
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"funkymonkUK"
wrote in message
...

Hi

I want to make the lastrow of sheet1 column A common knowledge between
my macros. Reason being is i want to refer the the lastrow a lot of
times

I thought maybe public sub would help this is my code

Public figs
' Count Rows in table
Dim lastRowpub As Long
lastRowpub = Worksheets("Sheet1").Cells(Rows.Count,
"A").End(xlUp).Row


It keeps coming up saying

Compile Error: Invalid Outside Procedure

and then highlights the (xlUp) part of the line


any ideas?


--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile:
http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=516165

questions about Dim
 

thanks

so would I need to run this sub each time before using the lastrow ?


--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile: http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=516165

questions about Dim
 

Yep, in fact it would be better as a function and call the function.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"funkymonkUK"
wrote in message
...

thanks

so would I need to run this sub each time before using the lastrow ?


--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile:
http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=516165

questions about Dim
 

how i do that?


--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile: http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=516165

questions about Dim
 

Function lastRowpub() As Long
' Count Rows in table
lastRowpub = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
End Function

You can use this function as follows:

set rng = cells(lasrRowpub(),"a")

HTH
--
AP

"funkymonkUK" a
écrit dans le message de
...

how i do that?


--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile:
http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=516165

questions about Dim
 

I would actually pass the sheet and column, to make it more generic

Function lastRowpub(colnum As Long, Optional sh As Worksheet) As Long
' Count Rows in table
If sh Is Nothing Then Set sh = Activesheet
lastRowpub = sh.Cells(sh.Rows.Count, colnum).End(xlUp).Row
End Function

Then use as

iLastRow = lastRowpub(1,Worksheets("Sheet1"))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ardus Petus" wrote in message
...
Function lastRowpub() As Long
' Count Rows in table
lastRowpub = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
End Function

You can use this function as follows:

set rng = cells(lasrRowpub(),"a")

HTH
--
AP

"funkymonkUK" a
écrit dans le message de
...

how i do that?


--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile:
http://www.excelforum.com/member.php...o&userid=18135
View this thread:
http://www.excelforum.com/showthread...hreadid=516165