If so; I would suggest to keep a separate worksheet where you can enter the
column numbers to be referenced. Write the code to pick the column numbers
from this worksheet; before executing the current code.

OR

As mentioned earlier; if you have a header in Row1. You can use find to get
the column number.

OR

Use the Input box so that the user can select the range.

Dim varRange As Range
Set varRange = Application.InputBox( _
Prompt:="Please select the range", Title:="Message", Type:=8)


If this post helps click Yes
---------------
Jacob Skaria


"DocBrown" wrote:

I'm getting the impression that this is the best way:

"OR always work with named ranges.."

I've used all the other techniques to handle when the user inserts rows
since the records and programming cycle through the rows.

This usually comes into play when I want to modify my WS and the code is,
say referencing column D. Then I insert a new row before D. Now the code is
referencing the wrong column, so I have to make sure I update the code.
Usually, the user never has to enter new columns. That up to me as the
designer of the ws.

What do you think of setting up global constants that set the values of the
columns I'm writing code against, then I just need to change the globals and
the code should work.

John

"Jacob Skaria" wrote:

For the deletion...

If you know the header atleast you can use Range.Find property to find the
column number.....OR always work with named ranges..

If this post helps click Yes
---------------
Jacob Skaria


"DocBrown" wrote:

I have seen many methods to reference ranges and cells in VBA. Also, in a WS
various techniques are used to allow for insertion and deletions of cells and
columns, such as relative and absolute addressing.

When rows and columns are changed in a WS, As far as I can tell, those
adjustments are not made in the Macros.

My question is: What is the best practice for coding functions and macros so
that they don't break if the rows and columns are changed in the worksheet we
are referencing?

Thanks,
John