Change Range using cell input

Chewyhairball

Active Member
Joined
Nov 30, 2017
Messages
312
Office Version
  1. 365
Platform
  1. Windows
Hi

Is there a was to change a named range according to a number in a cell.

I have Range1 in name manager going from A2:A11
1656511427832.png

1656511624401.png

1656511015478-png.68202


Can I define how many rows are in a range using the value in D4.
1656511488480.png

So then if i type 20 in cell D4 the range changes to A2:A21 etc...
1656511285351-png.68206
1656511530081.png
 

Attachments

  • 1656511448079.png
    1656511448079.png
    2.4 KB · Views: 2

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Can I define how many rows are in a range using the value in D4.
Is it D4 or D1?
Put this code in the sheet code module:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    ThisWorkbook.Names.Add Name:=Range("A1"), RefersTo:=Range("A2").Resize(Range("D1"))
End Sub

whenever you change the value in D1 the range in the named range will be adjusted.
 
Upvote 0
Solution
Is it D4 or D1?
Put this code in the sheet code module:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    ThisWorkbook.Names.Add Name:=Range("A1"), RefersTo:=Range("A2").Resize(Range("D1"))
End Sub

whenever you change the value in D1 the range in the named range will be adjusted.
D1…typo. Will give this a try thanks
 
Upvote 0
Is it D4 or D1?
Put this code in the sheet code module:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    ThisWorkbook.Names.Add Name:=Range("A1"), RefersTo:=Range("A2").Resize(Range("D1"))
End Sub

whenever you change the value in D1 the range in the named range will be adjusted.
Hi
This doesnt work for me. I keep getting this error
1656733771605.png
 
Upvote 0
It works for me:
2022-07-02_122254.jpg


Example:

Try it a a clean workbook first.

What's the actual value in A1?
 
Upvote 0
It works for me:
View attachment 68473

Example:

Try it a a clean workbook first.

What's the actual value in A1?
Got it working. Had deleted the name at the top of the range. Working Perfect.

Thank you so much :)
 
Upvote 0
@Chewyhairball
Sorry, I forgot something important. :(
We should specify cell D1 as the cell that will trigger the macro, otherwise a change in any cell will trigger it.
Like this:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    
If Target.Cells.CountLarge > 1 Then Exit Sub
    If Not Intersect(Target, Range("D1")) Is Nothing Then
           ThisWorkbook.Names.Add Name:=Range("A1"), RefersTo:=Range("A2").Resize(Range("D1"))
    End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

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