Deleting Rows/Columns Mess up my Recorder Macro

[jhnbrick]

Hello all - I have a recorder macro that auto-sorts data with a click of a button. The problem is; I can't add or delete any columns because then I have to re-do the recorder macro VBA language to fix the cells. Is there a way to allow the recorder macro to pick up the changes I.E. If I insert a row, the recorder macro vba will shift one cell over too!

 

[Zot]

It will help helpers out there if you can give sample sheet of Before and After to explain what you were trying to do. This will give clear picture.

 

[Michael M]

AND the current code being used !!

 

[jhnbrick]

Code!!!

Sub BrickC()
'
' BrickC Macro
' Refreshs My Calls
'

'
Range("X52:AD67").Select
ActiveWorkbook.Worksheets("Covered Calls").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Covered Calls").Sort.SortFields.Add2 Key:=Range( _
"AD45:AD59"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Covered Calls").Sort.SortFields.Add2 Key:=Range( _
"AB45:AB59"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Covered Calls").Sort
.SetRange Range("X52:AD67")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

 

[jhnbrick]

Here is the excel sheet ... my question is; if I insert a column in row A, then it pushes the cells to the right and messes up the recorder macro data! Is there a way to lock the cells or something?

 

[Zot]

If you define a named range on worksheet using the Name Manager whatever, no matter if you insert row or column, the named range will move along. It is dynamic.

Of course you can define only once during code run since you know the original location but after that you have to redefine the location. Defining in Excel is the possible option that I can think of right now ?