How to make a dynamic range with multiple tables on sheet?

Hi guys,

I want to make a dynamic range. I've found plenty of suggestions on the internet that use the offset function, and do things like count the number of non-blank cells in a column to get the the height of the dynamic range. The problem I have is that I have multiple tables on the same sheet, separated by blank cells, like this:

ExcelExample.png

I want to have two dynamic ranges. One for the first table named PureGasProperties, one for the second name MixtureProperties. If I use the count or counta function as many articles have suggested, it will count the non-blank cells in both tables rather than just the one I am interested in. What is the best way to count just the number of cells until the FIRST blank row? I have had a surprisingly difficult time finding an answer to this! I am hoping that a macros is not necessary and it can be done with excel function

Thanks!

Hi TMS, this would be valid in my example. I am hoping for something more robust, however. It is possible, for example, that I might want to add columns as well as rows at some point, and I would want some technique that would add these columns to the range as well. I might want to add the density or the viscosity as additional properties of the gases in addition to molecular weight, or I might want to add the composition of the mixtures. If the tables were placed next to each other I'd run into the same problem when I try to add columns dynamically. Apologies for not explaining this in the OP.