• Status: Solved
• Priority: Medium
• Security: Public
• Views: 2528

# Count Used rows in a closed workbook name range

I would like to count the number of used rows in name range("SEup") on sheet1 in a closed workbook in vba
0
Svgmassive
• 2
• 2
1 Solution

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

0

Commented:
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 Commented:
trying to do this using vba
0

Commented:
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
frmla2 = "ROW(" & addr & ")"
frmla3 = "MATCH(TRUE," & addr & "<>"""",0)"

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
``````

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 Commented:
excellent
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.