+ Reply to Thread
Results 1 to 8 of 8

Return number of rows used in a named range?

  1. #1
    Registered User
    Join Date
    08-03-2012
    Location
    Edmonton, Alberta, Canada
    MS-Off Ver
    Excel 2007
    Posts
    11

    Question Return number of rows used in a named range?

    Hi All,
    I have a named range ("Priority") which sits in cells K10:K21.
    Of these 12 cells I need to know how many are used so that I can set that value as how many times I paste the "EEName" from this sheet to another sheet.
    So if there are 6 lines filled out in "Priority" I need to copy "EEName", move to the Submissions tab, and paste the EEName 6 times into the next available cell in column A.

    The closest I think I can get is without using the named range.

    Please Login or Register  to view this content.
    When I finally find that value I think I can use this to paste it:
    Please Login or Register  to view this content.
    Anyone know how to finish what I started?
    Thanks in advance... unfortunately I can't upload anything from work. Total lockdown.

  2. #2
    Forum Contributor
    Join Date
    10-18-2012
    Location
    Telford, England
    MS-Off Ver
    Excel 2010 (2003)
    Posts
    294

    Re: Return number of rows used in a named range?

    For the first part: numrequired = application.worksheetfunction.counta(Priority)

  3. #3
    Registered User
    Join Date
    08-03-2012
    Location
    Edmonton, Alberta, Canada
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Return number of rows used in a named range?

    Close, yet it only posts the employee name once when there are 6 lines filled out.
    When I just do the counta(priority) function in the worksheet itself I get 6.
    I have even cleared the contents of the other cells just in case something snuck in there.
    Any idea why it would only post once?

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Return number of rows used in a named range?

    Why not just ...

    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Forum Contributor
    Join Date
    10-18-2012
    Location
    Telford, England
    MS-Off Ver
    Excel 2010 (2003)
    Posts
    294

    Re: Return number of rows used in a named range?

    I've looked at your
    Worksheets("Submissions").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Resize(NumEntries).Value = Range("EEName").Value
    ... several times and it's too complex for me to validate (I've never seen resize but I will look it up. Trying to write a range without copy is the challenge. I think shg is on the right track.

  6. #6
    Registered User
    Join Date
    08-03-2012
    Location
    Edmonton, Alberta, Canada
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Return number of rows used in a named range?

    Yes, actually that's what I needed shg. I was thinking I was copying a column with formulas all the way down and I only wanted blanks, but in this case it's not formulas so this does work.
    My only question is: why do I want to offset the paste line by (2) instead of just one? This does paste it below my data, but I thought End(xlUp) would put me on the last line of data so I only need to offset by (1)...
    And, worrying I will mess this up, how do I modify it *ever so slightly* so that it only pastes the values?

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Return number of rows used in a named range?

    ... so I only need to offset by (1)
    It's not an offset. In that syntax, (1) refers to the last used cell, and (2) to the cell below.
    And, worrying I will mess this up, how do I modify it *ever so slightly* so that it only pastes the values?
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    08-03-2012
    Location
    Edmonton, Alberta, Canada
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Return number of rows used in a named range?

    Thank you shg, I hadn't seen that syntax before but I will use that frequently in the future, I'm sure. Works like a charm!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1