Levi020497
asked on
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.
Levi
I also want to check the last row used in a worksheet.
Can anyone tell me how to do these things.
Levi
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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(x
counter = counter + 1
End If
Next
MsgBox counter & " worksheet(s) are being used"
For Each w In ActiveWorkbook.Worksheets
If Range("A1").SpecialCells(x
counter = counter + 1
End If
Next
MsgBox counter & " worksheet(s) are being 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