count filled and empty rows till the last filled row

[Dalal]

Hi all

I need help to edit a macro or to write a udf to do the follows:


i have a workbook with many sheets
in each sheet there is diffrent number of rows


I need :

1- a function to count all the rows in a sheet ( filled and blank rows in between the filled ones ) from the first row in the sheet to the last row filled with data .

I need this function to be able to insert a statement at the end of each sheet after the last row in each sheet

and because each sheet has a diffrent number of rows(either the blank rows or the filled ones ) i could not did this

I tried to use CountA ,CountBlank ,UsedRange property ,Range.Rows.count property
but bacause of the diffrentiation of each sheet this did not help

I am not professional in vba so maybe i did not use the right combination of these functions

so I guess that the right combination of these function will work
or maybe u can suggest better solution



Thaxxxxxxx in advance
i really appreciate your help

there is an example worksheet in the attachments

[protonLeah]

Please Login or Register  to view this content.

[MarvinP]

Hi Dalai,

See if the formulas in the attached work for you. They use an Indirect and Row() function to get the answer I think you want.

Please Login or Register  to view this content.

[Leith Ross]

Hello Dalal,

Here is the preffered method of finding the "true" last row. This is not influenced by the Excel's habit of "tracking" what you have done like formulas and the Range End method are.

Please Login or Register  to view this content.

[MarvinP]

Hi Leith,

I'm not as good with the Cells.Find method, so I'm trying to learn.

Your code of

Please Login or Register  to view this content.

seems to return the last value and not the number of rows.

What am I missing, as I know you are rarely wrong.

[Leith Ross]

Hello Marvin,

You are correct that the Cells.Find method returns the last entry in the last row. You will notice in the example that I define the variable LastRow as a Variant.This allows it to be set to the cell that contains the last entry. Next, LastRow is checked to see it is was set to cell value. If the worksheet is blank then LastRow equals the special object value Nothing and the default row is set to 1. Once the checks are made, LastRow is then assigned the row value. Using a variant allows the variable to go from a Range object to Long integer representing the row.

[MarvinP]

Thanks - I needed to see how you did that.

[Dalal]

Hi

Thxxxxxxxxx...All ProtonLeah ...Leith ...MarvinP

I tried all these solutions and they worked nicely


I,m learning nice new ideas from u all