Count worksheets in file and find last used row.

I want to make a macro in which I need to count the number of worksheets that are used in a specific excel file.
I also want to check the last row used in a worksheet.

Can anyone tell me how to do these things.

Who is Participating?
carttiConnect With a Mentor Commented:
Worksheets.Count       gives you the number of worksheets
Range("A1").SpecialCells(xlCellTypeLastCell).Row             returns the last used row. Assign this value to a variable for it to work.
If the rows used are continuous ie no gaps, you can use ActiveSheet.Range("A1").CurrentRegion.Rows.Count to get the last row used.
For the sheet count problem, you could scan every worksheet, checking every cell for an entry, but I would probably do something like; ensure that a certain identifying action has occured whenever a sheet is used (perhaps insist that the sheet is renamed from 'sheetXX' to something else). Then you could count all the worksheets whose names do not begin with the 5 letters 'Sheet', with:

Sub Countem()
Dim ws As Worksheet
Dim wsCount As Integer
wsCount = 0
For Each ws In Worksheets
   If Left(ws.Name, 5) <> "Sheet" Then
       wsCount = wsCount + 1
   End If
End Sub

By the way, if you are using Excel 95, change the constant xlCellTypeLastCell in SpecialCells method to xlLastCell
In my haste to answer your question, I misunderstood the first one. Use the SpecialCells last cell method, but swap the Row property to Address. If this is anything other than $A$1 then it's being used.
P.S. then do this method:

For Each w In ActiveWorkbook.Worksheets
   If Range("A1").SpecialCells(xlCellTypeLastCell).Address <> [A1].Address Then
      counter = counter + 1
   End If

MsgBox counter & " worksheet(s) are being used"
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.

All Courses

From novice to tech pro — start learning today.