PDA

View Full Version : Range References in VBA when Wks Rows/Columns Deleted



Opv
07-28-2010, 02:07 PM
I fear I know what the answer to this question is going to be but I am inclined to ask anyway. When rows and columns are deleted, references in worksheet formulas are automatically adjusted accordingly. However, that does not seem to be the case in VBA. I am curious as to whether there is a way to define ranges in VBA to avoid range references becoming obsolete when the structure of a worksheet is changed? Case in point:


Dim col As Integer: For col = 1 To gCols

Select Case col
Case 1, 2, 9, 10, 19, 20, 21, 22
Case Else
Columns(col).EntireColumn.Hidden = True
End Select
Next col



If Column 19 is deleted, only is the above case statement effected but all other functions and subroutines that reference Columns(19) are effected.

Bob Phillips
07-28-2010, 03:19 PM
Use defined names for the ranges, and use those names in VBA.