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
 
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.
 
Upvote 0

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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?
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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