Save last row to use in another set of code

[jonnybrovo815]

I run this bit of code to find the address of the the last cell in column K.

Sub test()
Dim x As Range
Worksheets("NCSA_ISS_ITEM_BOM").Activate
Set x = Cells(Rows.Count, "K").End(xlDown)
MsgBox x.Address
End Sub

I would like to save the resulting row address as a variable to run as part
of another set of code.

How would I go about doing this?

 

[Rick Rothstein]

If you move your Dim statement outside of the subroutine (that is, move it
into the General/Declarations section of the code module you are in), then
any code within that code module will be able to reference it. If you add a
Module to your project (Insert/Module from the VB editor's menu), then any
code in any of your project's code modules will be able to reference it.
However, I would give the variable a more targeted name as any variable
declarations for a variable with the same name within a sub or function will
over-ride an global variable you created by following my beginning advice.

 

[john]

Just declare x as a public variable you which sits outside your test
procedure. This will store the result which you can then use as required.

Run your test prog then run test2 - it should display the result.

Public x As Range
Sub test()

With Worksheets("NCSA_ISS_ITEM_BOM")

Set x = .Cells(.Rows.Count, "K").End(xlUp)

End With

End Sub

Sub test2()
MsgBox x.Address
End Sub

 

[Mike]

try this.

Sub lastRow()
MsgBox FindLastRow(Worksheets("Sheet1"), "A")
End Sub

Private Function FindLastRow(whatSheet As Worksheet, whichCol As String) As
Long
'this finds and returns the actual last row on a sheet
'that has entry in specified column
'NOT the next row available for data entry
'so calling routine should add 1 to the returned value
'to determine next row available for new entry
'when it is found that a sheet has no entries, this
'routine will (properly) return zero.

If Val(Left(Application.Version, 2)) < 12 Then
'in pre-2007 Excel
FindLastRow = whatSheet.Range(whichCol & Rows.Count).End(xlUp).Row
Else
'in Excel 2007 or later
FindLastRow = whatSheet.Range(whichCol &
Rows.CountLarge).End(xlUp).Row
End If
If FindLastRow = 1 And IsEmpty(whatSheet.Range(whichCol & "1")) Then
FindLastRow = 0 ' no entries at all in the column on the sheet
End If

End Function

 

[jonnybrovo815]

Thanks for your reply John- In the code that I provided I should have left
out the bit about the message box, sorry about that. Please remove the
message box to display the address of the last cell. I just want to store
the last cells row number and not the column part of the address. I want to
use that variable to then enter a formula into a range of cells based upon
the results from the last cell results. That way I don't have to drag the
formula down manually in the spreadsheet.

Here is the second bit of code
Sub PRED_ITEM_LEV_CHECK()

Range("AS9").Select
ActiveCell.FormulaR1C1 = _

"=IF(RC[-3]="""","""",RC[-3]=RC[-41]&RC[-40]&RC[-39]&RC[-38]&RC[-37]&RC[-36]&RC[-35])"
Range("AS10").Select
End Sub

 

[jonnybrovo815]

I am very green with thsi stuff. Could you show me an example of what you
are talking about?

 

[jonnybrovo815]

Thanks for your reply!

I have tried the below code you provided but it results in an error "Compile
error: Sub or function not defined"

I am a newbie so I am not sure why it doesn't like the "Sub lastRow ()"

Thanks for your help with this!

 

[Rick Rothstein]

I'm not sure why you wrote code to differentiate between XL2007 and earlier
version as Rows.Count returns the correct value in all those versions
(mainly because number of rows in a single column fits in a Long data type).
The Count property can handle up to 2047 full columns in XL2007 before the
CountLarge property is required, so you are well short of needing to use it.
The general rule is that only if you will have 2,147,483,648 or more cells
to count will you ever need to use CountLarge. With that said, you can
replace all of the code in your function with this...

With whatSheet
FindLastRow = .Cells(.Rows.Count, whichCol).End(xlUp).Row + _
(WorksheetFunction.CountA(.Columns(whichCol)) = 0)
End With

Using this code, your function will return 0 if there is no data in the
specified column and the actual row number for the last data in the column
if there is data there. I should also point out that, for both your original
code and my modification for it, if you have formulas in the column which
evaluate to the empty string (""), then those will be treated as data.