Range Reference for an Index

jwburritt

New Member
Joined
May 22, 2019
Messages
48
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,669
You could name C7, say myCell. Then:
Code:
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!
 
Last edited:

jwburritt

New Member
Joined
May 22, 2019
Messages
48
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,669
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:
Code:
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
 

Forum statistics

Threads
1,082,585
Messages
5,366,466
Members
400,892
Latest member
lamarh755

Some videos you may like

This Week's Hot Topics

Top