Results 1 to 3 of 3

VBA return number of rows from an external workbook

This is a discussion on VBA return number of rows from an external workbook within the Excel Questions forums, part of the Question Forums category; Hi I'm trying to determine the number of rows within a named range in an external workbook, to use in ...

  1. #1
    New Member
    Join Date
    Sep 2011
    Location
    Adelaide, South Australia
    Posts
    5

    Default VBA return number of rows from an external workbook

    Hi

    I'm trying to determine the number of rows within a named range in an external workbook, to use in my VBA code.

    I have tried the following approach but receive an error:

    Code:
    Dim rangeRows as Integer
    Dim rangeAddress as String
    rangeAddress = "'C:\Temp\Book1.xlsx'!NamedRange"
    rangeRows = Range(rangeAddress).Rows.Count
    Thanks for any help.

  2. #2
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    1,155

    Lightbulb Re: VBA return number of rows from an external workbook

    Hi
    Please test this:

    Code:
    Sub Andrew()
    
    
        Dim wb As Workbook
        Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & "Data2.xlsm")
        MsgBox wb.Names("Adelaide").RefersToRange.Rows.Count
        wb.Close
    
    
    End Sub
    Excel 2007 / Windows Vista (home)
    Excel 2003 / windows xp (work)
    Read Forum Rules here

  3. #3
    New Member
    Join Date
    Sep 2011
    Location
    Adelaide, South Australia
    Posts
    5

    Default Re: VBA return number of rows from an external workbook

    Quote Originally Posted by Worf View Post
    Hi
    Please test this:

    Code:
    Sub Andrew()
    
    
        Dim wb As Workbook
        Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & "Data2.xlsm")
        MsgBox wb.Names("Adelaide").RefersToRange.Rows.Count
        wb.Close
    
    
    End Sub
    Worf, thanks for your reply.

    The code works fine, except that it has to open the workbook rather than extract the information while it is closed.

    I have found a workaround that does the trick for now.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com