Select Named Range , depend on Cell Value

Rahul14181

New Member
Joined
Jul 24, 2017
Messages
6
Hello Everyone,

I need some urgent help regards to the subjected query: "Select Named Range , depend on Cell Value"

I have a searchable drop down at A1 and there is a button.

I have given name to specific range as below:

B5:C30 - Rahul
E5:D30 - Arvind
F5:G30 - Kumar

In drop down we have three name, (Rahul/Arvind/Kumar)... I want when A1 is Rahul and i click on button then B5:C30 get selected and vise versa for arvind and kumar too..

Please help
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
try:

Code:
Sub test()


Application.ScreenUpdating = False


Dim Ash As Worksheet
Dim stxt1 As String
Dim stxt2 As String
Dim stxt3 As String


Set Ash = ActiveWorkbook.ActiveSheet


stxt1 = "Rahul"
stxt2 = "Arvind"
stxt3 = "Kumar"


    If Ash.Cells(1, 2) = stxt1 Then
        With Ash
            .Range(.Cells(2, 5), .Cells(3, 30)).Select
        End With
    End If
    
    If Ash.Cells(1, 2) = stxt2 Then
        With Ash
            .Range(.Cells(4, 5), .Cells(5, 30)).Select
        End With
    End If
    
    If Ash.Cells(1, 2) = stxt3 Then
        With Ash
            .Range(.Cells(6, 5), .Cells(7, 30)).Select
        End With
    End If
   
Application.ScreenUpdating = True




End Sub
 
Upvote 0
OR

Code:
Sub test()


Application.ScreenUpdating = False


Dim Ash As Worksheet
Dim stxt1 As String
Dim stxt2 As String
Dim stxt3 As String


Set Ash = ActiveWorkbook.ActiveSheet


stxt1 = "Rahul"
stxt2 = "Arvind"
stxt3 = "Kumar"


    If Ash.Range("A1") = stxt1 Then
        With Ash
            .Range("B5:C30").Select
        End With
    End If
    
    If Ash.Range("A1") = stxt2 Then
        With Ash
            .Range("D5:E30").Select
        End With
    End If
    
    If Ash.Range("A1") = stxt3 Then
        With Ash
            .Range("F5:G30").Select
        End With
    End If
   
Application.ScreenUpdating = True




End Sub
 
Upvote 0
Thank you so much...!! its works..

But have one small query. i have a big data. so if i also want cursor to go highlighted range then what i have to do..

for example if i change the range from B5:C30 to B100:C100 in this scenario mentioned cell will get highlighted i m still on the top of the window no on B100..
 
Upvote 0
I am sorry but i think i mentioned my question in wrong way.

I want that when i click on button, it should take me to that selected name range.
 
Upvote 0
this one has variable row numbers that can be entered in B1 and C1, type any number on the cells and hit run.

Code:
Sub test()


Application.ScreenUpdating = False


Dim Ash As Worksheet
Dim stxt1 As String
Dim stxt2 As String
Dim stxt3 As String


Set Ash = ActiveWorkbook.ActiveSheet


stxt1 = "Rahul"
stxt2 = "Arvind"
stxt3 = "Kumar"
rnum = ActiveSheet.Range("B1").Value
cnum = ActiveSheet.Range("C1").Value


    If Ash.Cells(1, 1) = stxt1 Then
        With Ash
            .Range(.Cells(rnum, 2), .Cells(cnum, 3)).Select
        End With
    End If


    If Ash.Cells(1, 1) = stxt2 Then
        With Ash
            .Range(.Cells(rnum, 4), .Cells(cnum, 5)).Select
        End With
    End If


    If Ash.Cells(1, 1) = stxt3 Then
        With Ash
            .Range(.Cells(rnum, 6), .Cells(cnum, 7)).Select
        End With
    End If


Application.ScreenUpdating = True


End Sub
 
Upvote 0

Forum statistics

Threads
1,223,952
Messages
6,175,594
Members
452,656
Latest member
earth

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