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]

I don't know what you are trying to do with this line of code. UsedRange is a property, so this code doesn't do anything at all.

'Refresh UsedRange
  Worksheets("SAP").UsedRange

The only way I know of to keep an existing named range up to date when there are changes in the sheet is to use Worksheet_Change

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveWorkbook.Names("MyData").RefersToRange = "='" & Name & "'!" & UsedRange.Address
End Sub

 

[Martin sherk]

I don't know what you are trying to do with this line of code. UsedRange is a property, so this code doesn't do anything at all.

'Refresh UsedRange
  Worksheets("SAP").UsedRange

The only way I know of to keep an existing named range up to date when there are changes in the sheet is to use Worksheet_Change

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveWorkbook.Names("MyData").RefersToRange = "='" & Name & "'!" & UsedRange.Address
End Sub

I want to use a named range as my power query source, so i need a code to name a range so ican use it as my source.

 

[6StringJazzer]

The code I provided will update MyDate to be UsedRange every time there is a change in the sheet.

But the other line of code is deadwood and I still don't know what you expected that one line to do.

 

[Martin sherk]

The code I provided will update MyDate to be UsedRange every time there is a change in the sheet.

But the other line of code is deadwood and I still don't know what you expected that one line to do.

I don't have much expertise with VBA tbh so, may my code should be like the below and nothing else, right?

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveWorkbook.Names("MyData").RefersToRange = "='" & Name & "'!" & UsedRange.Address
End Sub

 

[Martin sherk]

The code I provided will update MyDate to be UsedRange every time there is a change in the sheet.

But the other line of code is deadwood and I still don't know what you expected that one line to do.

whenver i try to delete data using Ctrl + - , i get an error and also the shee turns into zeros

 

[6StringJazzer]

Instead of Name try Me.Name

Where did you put this code?

 

[Martin sherk]

Instead of Name try Me.Name

Where did you put this code?

my Code goes like this:

Sub update1()


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

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



'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

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveWorkbook.Names("MyData").RefersToRange = "='" & Me.Name & "'!" & UsedRange.Address
End Sub

 

[6StringJazzer]

Where did you put this code? That is, what module?

 

[Martin sherk]

Where did you put this code? That is, what module?

Not a module, but inside the current sheet am working on which is "SAP" sheet