Deleting Rows/Columns Mess up my Recorder Macro

jhnbrick

New Member
Joined
May 31, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,866
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
AND the current code being used !!
 

jhnbrick

New Member
Joined
May 31, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 31, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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?

1622512316006.png
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,866
Office Version
  1. 2016
Platform
  1. Windows
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 🙂
 

Forum statistics

Threads
1,147,571
Messages
5,741,887
Members
423,693
Latest member
Excelquestion35

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top