Solved

Count worksheets in file and find last used row.

Posted on 1999-01-29
5
829 Views
Last Modified: 2011-10-03
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.

Levi
0
Comment
Question by:Levi020497
  • 4
5 Comments
 
LVL 3

Expert Comment

by:sduckett
ID: 1616088
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
Next
End Sub

0
 
LVL 2

Accepted Solution

by:
cartti earned 100 total points
ID: 1616089
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.
0
 
LVL 2

Expert Comment

by:cartti
ID: 1616090
By the way, if you are using Excel 95, change the constant xlCellTypeLastCell in SpecialCells method to xlLastCell
0
 
LVL 2

Expert Comment

by:cartti
ID: 1616091
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.
0
 
LVL 2

Expert Comment

by:cartti
ID: 1616092
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
Next

MsgBox counter & " worksheet(s) are being used"
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

Have you ever had the experience that you had to follow 10 steps over and over again every time when you need to nicely forward an important email to your manager? Fear no more! With the help of the Quick Steps feature in Outlook 2010, your old chor…
My experience with Windows 10 over a one year period and suggestions for smooth operation
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.

679 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