Count all used rows in select worksheets

[colin.dickson]

Hi,

I'm looking for a MsgBox that will tell me how many used rows there are in an array of selected worksheets, ignoring the first row...?

Regards

Colin

[TMS]

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

[colin.dickson]

See attached...

the answer would be 18 - count of all used rows in column A in all worksheets excluding header

[mohadin]

Hi
Try

Please Login or Register  to view this content.

[colin.dickson]

That works perfect... is it possible to ignore the first 2 worksheets?

Also to provide a total of the values in column D on the same msgbox?

[colin.dickson]

So i've amended the above to only look at sheets 3 onwards... it's just the total of the values in column D?

[TMS]

So, you said your expected result is 18. In your example, that's three cells in column A on each of six sheets.

Now your requirement is to count cells in column D on sheets 3 onwards. There is no data in column D on any of the sheets.

And you also need to be aware that moving the sheets around could affect the results.

Do the sheets have names, other than Sheet1, Sheet2, Sheet3, etc. It would be safer if the code referred to specific named sheets.

[mohadin]

Please Login or Register  to view this content.

[colin.dickson]

Sorry, revised workbook attached... sum of column B, not D.

So in this case it would be 18 and 36

The worksheets will not move so that should be ok, thanks..

[AB33]

Do you want to count the number of cells or add the cells in column B ?

[colin.dickson]

the sum of the values...

[TMS]

4 sheets, 3 cells in column B each is 12. If you are summing the values, rather than counting them, it's 24.

Has your requirement changed again?

[colin.dickson]

there are 6 sheets with three rows each containing the value 2 (6*3*2) = 36?

[AB33]

It is Mohadin's code— I do not take the credit just twicked it.

Please Login or Register  to view this content.

[TMS]

there are 6 sheets with three rows each containing the value 2 (6*3*2) = 36?

Yes, and in post #5 you said:

... is it possible to ignore the first 2 worksheets?