?
Solved

Count worksheets in file and find last used row.

Posted on 1999-01-29
5
Medium Priority
?
834 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 300 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
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…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

862 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