Solved

Count worksheets in file and find last used row.

Posted on 1999-01-29
5
826 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction It seems that at least a couple of times per month, I answer a question that requires automating Outlook from another Microsoft Office application, usually (although not always) to send one or more email messages.  For example: …
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
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 simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

895 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now