Range Reference for an Index

[jwburritt]

Hello: The following codes finds the last row in a table and then creates a simple numerical index starting with 1. A couple of things:

==> Is there a way to refer to column C without hard coding it so I can add and remove columns without a problem?
==> Is there a way to refer to cell C7 without hard coding it so I can add and remove rows without a problem?

I've used the named range "data_index" in another range, but can't seem to get it to solve the above.

Thank you!


============================

Sub index_fill()


Call reset

Range("C" & Rows.Count).End(xlUp).Select

Range(Selection, "C7").Select

Selection.ClearContents

Range("data_index").Offset(1, 0).Value = 1

Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
Step:=1, Trend:=False

Range("data_date").Select



End Sub

 

[JoeMo]

You could name C7, say myCell. Then:

Sub index_fill()


Call Reset

Range("myCell", Cells(Rows.Count, Range("myCell").Column).End(xlUp)).Select
Selection.ClearContents
Range("data_index").Offset(1, 0).Value = 1

Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
Step:=1, Trend:=False

Range("data_date").Select



End Sub

Be careful in deleting rows that you don't delete the row myCell is in!

 

[jwburritt]

Thanks, Joe. But any number of rows could be deleted beginning with row 7. That's why I was trying to get to C7 by offsetting from C6, which is the named range "data_index."

 

[JoeMo]

Thanks, Joe. But any number of rows could be deleted beginning with row 7. That's why I was trying to get to C7 by offsetting from C6, which is the named range "data_index."

Maybe:

Range(Range("data_index").Offset(1, 0), Cells(Rows.Count, Range("data_index").Column).End(xlUp)).Select
Selection.ClearContents
Range("data_index").Offset(1, 0).Value = 1

 

[jwburritt]

Thanks, Joe. Perfect!

 

[JoeMo]

Thanks, Joe. Perfect!

You are welcome - thanks for the reply.