How to automatically update a used range with a name

[Martin sherk]

I use the below code to update a used range and name it, I need a way to automate this process when users delete data in that sheet and add new data, I want VBA to be able to automatically name that new used range without having to run the code every time.

Sub update()


Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range

Set sht = Worksheets("SAP")
Set StartCell = Range("A1")

'Refresh UsedRange
  Worksheets("SAP").UsedRange

'Find Last Row and Column
  LastRow = StartCell.SpecialCells(xlCellTypeLastCell).Row
  LastColumn = StartCell.SpecialCells(xlCellTypeLastCell).Column

'Select Range
  sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select
  sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Name = "MyData"
End Sub

 

[6StringJazzer]

That's a module. It's a worksheet module. That is the correct place for it to go.

There is nothing in my code--or yours either--that changes the content of any cells. I can't explain why a CTRL+- would turn cells to zeroes.

 

[Martin sherk]

h

That's a module. It's a worksheet module. That is the correct place for it to go.

There is nothing in my code--or yours either--that changes the content of any cells. I can't explain why a CTRL+- would turn cells to zeroes.

is there a way to refresh code automatically each time the old data is deleted and a new data is added so it can name the new range, if not . should i make a macro button for it?