Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Count Used rows in a closed workbook name range

Posted on 2013-12-12
5
2,061 Views
Last Modified: 2014-02-20
I would like to count the number of used rows in name range("SEup") on sheet1 in a closed workbook in vba
0
Comment
Question by:Svgmassive
  • 2
  • 2
5 Comments
 
LVL 14

Expert Comment

by:peetjh
ID: 39715237
Check post #5 on this page in Excel MVP Purna Duggirala  (who goes by the screen name Chandoo) forum:

http://forum.chandoo.org/threads/how-to-count-the-number-of-rows-without-opening-an-excel-file.11887/
0
 
LVL 81

Expert Comment

by:byundt
ID: 39715655
Here is a worksheet formula that can return the number of the last row with data in column A in a closed workbook. It does not have to be array-entered, but you should restrict the number of rows being searched if you are concerned about calculation speed.
=LOOKUP(2,1/('X:\VBA\Sample ''13\[Book4.xlsm]Sheet1'!A1:A10000<>""),ROW('X:\VBA\Sample ''13\[Book4.xlsm]Sheet1'!A1:A10000))

The pattern is this:
=LOOKUP(2,1/(closed workbook range<>""),ROW(closed workbook range))

The second parameter will either be 1 (data is in that row) or #DIV/0! error value (no data in that row). LOOKUP ignores the error values and keeps looking for a 2. Since it will never find a 2, it finds a match with the last row containing data. The corresponding element of the third parameter is the row number of that row.
0
 

Author Comment

by:Svgmassive
ID: 39745142
trying to do this using vba
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39745432
The following code is working in my test workbook. It displays a MsgBox with the number of used rows in the named range SEup in a closed workbook.

There are two ways used. One of them should work in Excel 2007 or later, while the second requires Excel 2010 or later. The first one will work in Excel 2003 if SEup is not an entire column.

Both methods put a formula in a worksheet cell, then use the results of that formula to capture the value.
Sub ClosedWorkbookRowCounter()
Dim addr As String, frmla As String, frmla1 As String, frmla2 As String, frmla3 As String, frmla4 As String, _
    flPath As String, flName As String, shName As String, rngName As String
Dim i As Long
flPath = "X:\VBA\Sample '13\"
flName = "TestWorkbook.xlsx"
shName = "Sheet1"
rngName = "SEup"

addr = "'" & Replace(flPath & "[" & flName & "]" & shName, "'", "''") & "'!" & rngName
frmla1 = "LOOKUP(2,1/(" & addr & "<>""""),ROW(" & addr & "))"
frmla2 = "ROW(" & addr & ")"
frmla3 = "MATCH(TRUE," & addr & "<>"""",0)"
frmla4 = "AGGREGATE(15,6,ROW(" & addr & ")/(" & addr & "<>""""),1)"

With ActiveSheet
    With .Cells(.Rows.Count, .Columns.Count)       'OK in any version of Excel
        frmla = "=" & frmla3
        .FormulaArray = frmla
        frmla = "=" & frmla1 & " - (" & frmla2 & " + " & .Value & " - 2)"
        .Formula = frmla
        MsgBox .Value
        .EntireRow.Delete
    End With
    .Columns(.Columns.Count).Delete
    
    With .Cells(.Rows.Count, .Columns.Count)       'Requires Excel 2010 or later for AGGREGATE function
        frmla = "=" & frmla1 & " - " & frmla4 & " + 1"
        .Formula = frmla
        MsgBox .Value
        .EntireRow.Delete
    End With
    .Columns(.Columns.Count).Delete
    
    i = .UsedRange.Rows.Count + .UsedRange.Columns.Count    'Resets the scrollbars
End With
End Sub

Open in new window


It should be noted that I initially got a run-time error with the first method because the formula length exceeded 255 characters. I broke the formula up into pieces to get the length short enough to work.
0
 

Author Closing Comment

by:Svgmassive
ID: 39874406
excellent
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This collection of functions covers all the normal rounding methods of just about any numeric value.
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

840 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