Deleting Columns - Causes Code Changes

[rick]

When I delete columns, it causes me to go through all of my code and
update it for the new columns.

Question 1:
I know I need to create a Named Range for the column, but do I define
it like this for the entire column? or do I do it just for the first
cell of that column?

This does the whole column =Master!$B:$B but later on if I refer
to this column ("test") can I refer to just one row?

Question 2:
Every time I use the autofilter it doesn't take column letters,
rather column numbers... ex: Selection.AutoFilter Field:=20

How can I use my named range to refer to column 20?

Question 3:
When doing row update I often use code like CurCell.Offset(0,
3).Value so when I add/delete columns I have to go update all of
this code... is there a way to use my named column range and Not use
the offset code?

thanks!

 

[crferguson]

When I delete columns, it causes me to go through all of my code and
update it for the new columns.

Question 1:
I know I need to create a Named Range for the column, but do I define
it like this for the entire column? or do I do it just for the first
cell of that column?

This does the whole column =Master!$B:$B but later on if I refer
to this column ("test") can I refer to just one row?

Question 2:
Every time I use the autofilter it doesn't take column letters,
rather column numbers... ex: Selection.AutoFilter Field:=20

How can I use my named range to refer to column 20?

Question 3:
When doing row update I often use code like CurCell.Offset(0,
3).Value so when I add/delete columns I have to go update all of
this code... is there a way to use my named column range and Not use
the offset code?

thanks!

Can you post a little about what you're trying to accomplish? It's a
little vague asking specific questions without any background. It's
kind of like asking, "Why is the color blue?" without mentioning that
you're talking about the sky.

 

[rick]

1. Range("C" & nRow).Select - If I wanted to use a named range to
refer to column C do I define it as "Master!$C:$C"? Because in this
case i want to refer to a specific row in that column.

2. How do I use my named range when using the Autofilter?
Selection.AutoFilter Field:=20 since it takes a coumn number not a
Column letter.

3. Same issue with using offset, it takes a column number not letter,
so how can i use a named range in the offset call?

 

[crferguson]

1. Range("C" & nRow).Select - If I wanted to use a named range to
refer to column C do I define it as "Master!$C:$C"? Because in this
case i want to refer to a specific row in that column.

I think I see where you're going, but using a named range to fix the
problem with deleting columns isn't going to work. If you delete a
column, you also delete it's name if you've named the column as a
range. But, either way, to answer this #1...

Range("MyNamedRangesName").Row(nRow).Select

2. How do I use my named range when using the Autofilter?
Selection.AutoFilter Field:=20 since it takes a coumn number not a
Column letter.

Dim nColumn, r as Range

Set r = Range("MyNamedRangesName")
nColumn = r.Column
r.AutoFilter Field:=nColumn, Criteria1:="YourCriteria"

3. Same issue with using offset, it takes a column number not letter,
so how can i use a named range in the offset call?

This doesn't make much sense without knowing what you're trying to do,
but you can see in #2's answer how to get a column number from the
named range.

Hope something in that helps..
Cory

 

[Susan]

another idea is using something **in** the column itself to set the
range. like say the column you want the named range to be in always
contains the word "small" somewhere in the column. set the range by
using FIND; then no matter if "small" is found in Column C or in
Column F, it will be correct.
susan