Excel Macro: Defining Non Active Page

Posted on 2014-09-07
Last Modified: 2014-09-07

I have the following line of code. I would like to define that the lastrow range is from another (non active sheet) named 'Unique ID'. How would I modify the code in line 3?

Dim lastrow As Long

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

Open in new window

Question by:dabug80
LVL 22

Expert Comment

ID: 40309136
Add the sheet name:

Dim lastrow As Long

    lastrow = Range("UniqueID!F" & Rows.Count).End(xlUp).Row

LVL 50

Accepted Solution

teylyn earned 500 total points
ID: 40309142

Sub test()
Dim lastrow As Long
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("UniqueID")

    lastrow = ws.Range("F" & Rows.Count).End(xlUp).Row
    MsgBox lastrow

End Sub

Open in new window

@Flyster, your suggestion does not work in a sheet module.

cheers, teylyn

Author Closing Comment

ID: 40309199
Thanks for your help

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now