Macro to Determine Length from Middle of Column

[rmmohan]

I currently have a post that has been unanswered, so in trying to work it out myself I have a more specific question.
In the middle of my spreadsheet are two columns of data I'm working with, that will eventually be involved in an if statement.

I need a macro to count the cells filled in Column G for example versus Column H, except it won't be from G1 or H1.
It'll always be the same starting point, so let's say G20 and H20.
So the macro should somehow select that cell then count how long that range is and be able to dimension it.

Then my if statement could say if rangeG > rangeH then ect.

So basically I need a code to dimension a range by counting it, but to be able to start from the middle of the range.
This is what I currently have that won't work for any starting point except G1:

Please Login or Register  to view this content.

Thank you in advance!!

[Richard Buttrey]

Hi,

Can't you just use

=COUNTA(G20:G100)

or whatever your range is?

[rmmohan]

The length of the range will be changing. So I need the macro to determine the length having only been given the starting cell.

[jonboy6257]

Would it be possible to divide rangeG in half and then apply that to a new starting range as opposed to G1? I'm not on acomputer with excel at the moment or I would try it out.

[rmmohan]

I'm not sure. Another idea I had was to count the entire column G, then subtract the known starting range of G1:G20.
But I don't know if it's possible to have a range dimensioned like that??