Function to count rows in column A

[bd528]

Hi,

In the code I am writing, I am often having to count the number of used rows in column A (there are no blanks). I'd therefore like a function to automate this. Something along the lines of :-

Please Login or Register  to view this content.

How would I code the function, and then refer to it in the code?

Thanks in advance

[jeffreybrown]

The most common way is probably to use...

This example look down column A and finds the end row and returns it as a number and then is used as a variable in the For Next loop

Please Login or Register  to view this content.

Here is a reference but there are many others...

http://www.xldynamic.com/source/xld.LastValue.html#vba

[contaminated]

If you have no blanks within range A:A then

Please Login or Register  to view this content.

This will return last non-empty row number.

U can also use CountA function to determine amount of non-empty cells which will be equal last non-empty cell row number.

Please Login or Register  to view this content.

[contaminated]

@jeffreybrown

FYI. To delete blank rows you can actually first select all blank cells within a range then delete them

Please Login or Register  to view this content.

Or

Please Login or Register  to view this content.

IMO this much easier and faster than looping through all blanks cells.

[jeffreybrown]

Vusal,

Thanks for the help...

Must admit still trying to learn all the different ways...

[bd528]

Thanks for the feedback - I will go with the following option :-

Please Login or Register  to view this content.

If anyone has the time though, could someone still explain how to do this via custom function, refering the sheet's name. I'd like to be able to understand how to send the sheets name to the function and then work on that sheet

Thanks

[contaminated]

Please Login or Register  to view this content.

[bd528]

Please Login or Register  to view this content.

Exactly what I needed. Thank you.