How to automatically update a used range with a name

Martin sherk

Board Regular
Joined
Sep 11, 2022
Messages
94
Office Version
  1. 365
  2. 2016
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.

VBA Code:
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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.
VBA Code:
'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
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveWorkbook.Names("MyData").RefersToRange = "='" & Name & "'!" & UsedRange.Address
End Sub
 
Upvote 0
Solution
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.
VBA Code:
'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
VBA Code:
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.
 
Upvote 0
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.
 
Upvote 0
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?

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveWorkbook.Names("MyData").RefersToRange = "='" & Name & "'!" & UsedRange.Address
End Sub
 
Upvote 0
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
 

Attachments

  • 1670111567010.png
    1670111567010.png
    23.3 KB · Views: 4
  • 1670111680899.png
    1670111680899.png
    6.2 KB · Views: 4
Upvote 0
Instead of Name try Me.Name

Where did you put this code?
my Code goes like this:

Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,157
Members
453,021
Latest member
Justyna P

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
Back
Top