Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Count worksheets in file and find last used row.

Posted on 1999-01-29
5
Medium Priority
?
833 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

618 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