Use calculated cell as part of range reference

  • Thread starter Thread starter Sandy
  • Start date Start date
S

Sandy

I have the following -

Sheets("Search Rounds").Range("H8:AD10000").ClearContents

which does the job but runs very slowly.

I have in cell H6 the following -

=COUNTA(H8:H10000)

How can I incorporate the value from H6 into ("H8:AD10000") to give the
actual populated range that I want to clear?

I understand there is also a way using End(xlup) - I think!? - and if so
which would be faster to process?

Thanks
Sandy
 
Why not use a defined name? Insert>name>define>name it myrng>in the refers
to box type
=OFFSET($H$7,1,0,COUNTA($H:$H)-7,23)
Now the range will be self adjusting based on the last row in col H
 
Excellent solution Don.
Thank you
Sandy

Don Guillett said:
Why not use a defined name? Insert>name>define>name it myrng>in the refers
to box type
=OFFSET($H$7,1,0,COUNTA($H:$H)-7,23)
Now the range will be self adjusting based on the last row in col H
Click to expand...
 
Back
Top