I have the following macro:

Sub TCAPSfcst()
' Hides rows 85:120
' Hides columns AM:CP
' Freezes window at J4
Cells.Select
Range("B1").Activate
Selection.EntireRow.Hidden = False
Selection.EntireColumn.Hidden = False
Rows("85:120").Select
Selection.EntireRow.Hidden = True
Columns("AM:CP").Select
Selection.EntireColumn.Hidden = True
Range("J4").Select
ActiveWindow.FreezePanes = False
ActiveWindow.FreezePanes = True
End Sub

If I add or delete a column or row to this spreadsheet, the macro above
doesn't update its references. I now need to hide rows 85:120, hide columns
AM:CN, and freeze the window at cell H4.

This is easy enough to do manually by going into this code, and changing the
cell references accordingly. However, I am wondering if there is a way for
XL to dynamically update the code as rows and/or columns are added or deleted?

Thanks,

Dave


--
Brevity is the soul of wit.

If you use Named Ranges rather than absolute row/column references, then the
macro will adapt (because the Named Ranges will also adapt)
--
Gary's Student


"Dave F" wrote:

I have the following macro:

Sub TCAPSfcst()
' Hides rows 85:120
' Hides columns AM:CP
' Freezes window at J4
Cells.Select
Range("B1").Activate
Selection.EntireRow.Hidden = False
Selection.EntireColumn.Hidden = False
Rows("85:120").Select
Selection.EntireRow.Hidden = True
Columns("AM:CP").Select
Selection.EntireColumn.Hidden = True
Range("J4").Select
ActiveWindow.FreezePanes = False
ActiveWindow.FreezePanes = True
End Sub

If I add or delete a column or row to this spreadsheet, the macro above
doesn't update its references. I now need to hide rows 85:120, hide columns
AM:CN, and freeze the window at cell H4.

This is easy enough to do manually by going into this code, and changing the
cell references accordingly. However, I am wondering if there is a way for
XL to dynamically update the code as rows and/or columns are added or deleted?

Thanks,

Dave


--
Brevity is the soul of wit.

Perfect, thanks.

Dave
--
Brevity is the soul of wit.


"Gary''s Student" wrote:

If you use Named Ranges rather than absolute row/column references, then the
macro will adapt (because the Named Ranges will also adapt)
--
Gary's Student


"Dave F" wrote:

I have the following macro:

Sub TCAPSfcst()
' Hides rows 85:120
' Hides columns AM:CP
' Freezes window at J4
Cells.Select
Range("B1").Activate
Selection.EntireRow.Hidden = False
Selection.EntireColumn.Hidden = False
Rows("85:120").Select
Selection.EntireRow.Hidden = True
Columns("AM:CP").Select
Selection.EntireColumn.Hidden = True
Range("J4").Select
ActiveWindow.FreezePanes = False
ActiveWindow.FreezePanes = True
End Sub

If I add or delete a column or row to this spreadsheet, the macro above
doesn't update its references. I now need to hide rows 85:120, hide columns
AM:CN, and freeze the window at cell H4.

This is easy enough to do manually by going into this code, and changing the
cell references accordingly. However, I am wondering if there is a way for
XL to dynamically update the code as rows and/or columns are added or deleted?

Thanks,

Dave


--
Brevity is the soul of wit.

You are very welcome. By the way, it is a good way to mark the end of
UsedRange in a set of worksheets.
--
Gary's Student


"Dave F" wrote:

Perfect, thanks.

Dave
--
Brevity is the soul of wit.


"Gary''s Student" wrote:

If you use Named Ranges rather than absolute row/column references, then the
macro will adapt (because the Named Ranges will also adapt)
--
Gary's Student


"Dave F" wrote:

I have the following macro:

Sub TCAPSfcst()
' Hides rows 85:120
' Hides columns AM:CP
' Freezes window at J4
Cells.Select
Range("B1").Activate
Selection.EntireRow.Hidden = False
Selection.EntireColumn.Hidden = False
Rows("85:120").Select
Selection.EntireRow.Hidden = True
Columns("AM:CP").Select
Selection.EntireColumn.Hidden = True
Range("J4").Select
ActiveWindow.FreezePanes = False
ActiveWindow.FreezePanes = True
End Sub

If I add or delete a column or row to this spreadsheet, the macro above
doesn't update its references. I now need to hide rows 85:120, hide columns
AM:CN, and freeze the window at cell H4.

This is easy enough to do manually by going into this code, and changing the
cell references accordingly. However, I am wondering if there is a way for
XL to dynamically update the code as rows and/or columns are added or deleted?

Thanks,

Dave


--
Brevity is the soul of wit.