Count Number of Rows in a Workbook

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello Everyone,

Can anyone tell me how to run a macro that will:

* open a workbook
* count the number of rows
* write this number in the first workbook

thanks!
Dave
 
Dave,

Try something like the following. Change the workbook, worksheet, and range
references in the lines marked with '<<<< to the appropriate values.

Sub AAA()
Dim SaveWB As Workbook
Dim WB As Workbook
Dim WS As Worksheet
Dim Rng As Range
Dim NumRows As Long

Set SaveWB = ActiveWorkbook
Set WB = Workbooks.Open("C:\Test\Book1.xls") '<<<< CHANGE
Set WS = WB.Worksheets("Sheet1") '<<<< CHANGE
With WS.UsedRange
NumRows = .Cells(.Cells.Count).Row - .Cells(1, 1).Row + 1
End With
WB.Close savechanges:=False
SaveWB.Worksheets("Sheet1").Range("A1").Value = NumRows '<<<< CHANGE
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
Back
Top