Range("K65536").End(x1Up).Select ....... How to use same row count elswhere?

[Apache_sim]

A third party program we use produces a report to Excel 2010, the number rows of data varies each week.

A macro is run on the report which returns using countif() the number of instances of 5 differeing strings.

Using 'Range("K65536").End(x1Up).Select', which works out the last cell containing data.
and the using 'ActiveCell.Formula = "=Countif(K:K,""string"")"

Question: I wish to use the same row count above to sum another column of numerical data of which only a few rows contain data.

If I Use:
'Range("G65536").End(x1Up).Select'
'ActiveCell.Formula = "=Sum(G:G)"
The data is entered in the next empty cell and not on the same row as column K. I want to use the same row count as K:K..
Any Ideas

Regards

[JasperD]

Try as follows

Please Login or Register  to view this content.

[nilem]

maybe something like

Please Login or Register  to view this content.

[patel45]

Please Login or Register  to view this content.

[HaHoBe]

Hi, patel45,

your code should create a circular refernce as the cells you write the formula to are included in the range of what is to be summed (as well as JasperD code for sum), and the second formula is not correct (should be CountIf not Sum).

Ciao,
Holger

[Apache_sim]

Thanks for the help I used the suggestion by JasperD and modified it using an offset to place the result in the correct position.
However, I am sure all the suggestions would also have worked as well.

How do I now show the Thread as solved I can't seem to find that option?

Thanks