Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Columns - Causes Code Changes
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Columns - Causes Code Changes
On Dec 11, 12:03 pm, "
wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Columns - Causes Code Changes
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Columns - Causes Code Changes
On Dec 11, 1:29 pm, " wrote:
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Columns - Causes Code Changes
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 On Dec 11, 3:13 pm, " wrote: On Dec 11, 1:29 pm, " wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding Columns, Then deleting old columns | Excel Discussion (Misc queries) | |||
Combining Text from 2 Columns into 1 then Deleting the 2 Columns | Excel Worksheet Functions | |||
Deleting rows in two columns with a code in visual basic | Excel Discussion (Misc queries) | |||
VBA Code for Deleting a Row | Excel Worksheet Functions | |||
Deleting code in a file with code.. | Excel Programming |