Get the List of Data Name Range

[NimishK]

Hello

How can I get a list of Data Name Range from the following representation ?
1. Columns are fixed (A:D)
2. Rows are unpredictable: TableDataName Range is also unpredictable
3. Data Name Range to be named from Firms Name ie from COL A which is hilited in bg color for reference or From COL
B ie REF Code
4. Firm’s Name will begin every 1^st row in COL A after the previous Range
5. ROW 2 is the Header Row

1 A B C D 2 Firm Name REF CODE TRANS CODE Goods Description 3 ABC 1 88 fdsfdssfdsfdsfds 4 23 dfdsdsfdsfds 5 XYZ 2 1 fddsfdsdsffdsf 6 2 fdsfkdsjfhdskjfds fhdbfdskhfdsf 7 3 fdsfdfdsfdsfdfdds 8 AAA 43 44 fffffff44ffffffff 9 55 55ggggggggg 10 23 11 77 Yrtytytryrtryt 12 88 Fdsfdssfdsfdsfds 13 TMOT 55 88 fdsfdssfdsfdsfds <tbody> </tbody>

<tbody>
</tbody>

Data Name Range List will Include ABC, XYZ, AAA, TMOT with its Range as per the above representation
List to be displayed in Combo or Listbox.
for EG XYZ A5:D7

Pl excuse me if the format has not come clear

NimishK

 

[MickG]

This code will Name the ranges and fill Combobox1 with the names and Addresses.

[COLOR="Navy"]Sub[/COLOR] MG08Sep36
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Temp = IIf(Dn.Value = "", Temp, Dn.Value)
        [COLOR="Navy"]If[/COLOR] Not .Exists(Temp) [COLOR="Navy"]Then[/COLOR]
            .Add Temp, Dn
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]Set[/COLOR] .Item(Temp) = Union(.Item(Temp), Dn)
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Dim[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant
ReDim Ray(1 To .Count)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
   c = c + 1
   .Item(K).Resize(, 4).Name = K
    Ray(c) = K & " " & .Item(K).Resize(, 4).Address(0, 0)
[COLOR="Navy"]Next[/COLOR] K
ComboBox1.List = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

This code will Select the range from the selection in ComboBox1.

Private Sub ComboBox1_Change()
Range(Split(ComboBox1.Value, " ")(1)).Select
End Sub

 

[NimishK]

Thankx MickG this was Mind Blowing One., Thank you so much

 

[Peter_SSs]

If I have understood correctly, you could also use this more direct method to get the 'Ray' array to populate the ComboBox.

Sub GetFirms()
  Dim Ray() As String
  Dim c As Range, LastA As Range
  Dim rws As Long, k As Long
  
  Set LastA = Range("A" & Range("D" & Rows.Count).End(xlUp).Row)
  For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants)
    rws = 1
    If IsEmpty(c.Offset(1).Value) And c.Address <> LastA.Address Then rws = rws + Range(c, LastA).SpecialCells(xlBlanks).Areas(1).Rows.Count
    k = k + 1
    ReDim Preserve Ray(1 To k)
    Ray(k) = c.Value & " " & c.Resize(rws, 4).Address(0, 0)
  Next c
  ComboBox1.List = Ray
End Sub

 

[MickG]

You're welcome

 

[NimishK]

Thanks Peter. your code also really works

Sorry for late reply.

 

[Peter_SSs]

Thanks Peter. your code also really works

Sorry for late reply.

No problem. Glad to contribute.

 

[NimishK]

Peter

Indeed this was great one.
I was wondering if i wanted the Value of Next Adjacent Cell ie Value of Cell B after Getting the Firms Name. What needs to be done
I tried the following got Type Mismatch

Ray(k) = Cells(rws, 2).Value

 

[Peter_SSs]

.. after Getting the Firms Name.

Are you looking for a vba solution? Where/how are you "getting" the firm's name? Is it stored in a variable? What variable? Have you got some existing code you could share with us?


Or are you looking for a worksheet formula? Where (what cell?) do you have the firm name that you are interested in?

 

[NimishK]

Peter
After trying your code. Thought changed to get one more column incorparated i.e col B with Ref No.
I thought of Keeping Refernce No as Main Ref because wanted to get its Ref. No(s) automatically generated Ref.No on Userform comboRefNo.Text
As of Now the Firms are Two addeditem to Combobox in future may be added more. So when i Select the Firms Name via Combobox. comboRefNo.Text should display its Automatically Add 1 to Last Ref. No.

Private Sub UserForm_Initialize()

Combobox1.Text = "ABC"
Combobox1.Additem = "ABC"
Combobox1.Additem = "XYZ"

If Combobox1.Text = "ABC" Then
   Userform1.ComboRefNo.Value = Format(Val(Cells(NEWREFERENCE, 2).End(xlUp)) + 1, "0000")
''''Have inocrporated NEWREFERENCE instead of  Rows.Count    
End If

If Combobox1.Text = "XYZ" Then
   Userform1.ComboRefNo.Value =  Format(Val(Cells(NEWREFERENCE, 2).End(xlUp)) + 1, "0000")    
''''Have inocrporated NEWREFERENCE instead of  Rows.Count    
End If

End Sub
'''''Your Code below
Sub GetFirms()
  Dim Ray() As String
  Dim c As Range, LastA As Range
  Dim rws As Long, k As Long
  
  Set LastA = Range("A" & Range("D" & Rows.Count).End(xlUp).Row)
  For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants)
    rws = 1
    If IsEmpty(c.Offset(1).Value) And c.Address <> LastA.Address Then rws = rws + Range(c, LastA).SpecialCells(xlBlanks).Areas(1).Rows.Count
    k = k + 1
    ReDim Preserve Ray(1 To k)
    Ray(k) = c.Value & " " & c.Resize(rws, 4).Address(0, 0)
  Next c
  ComboBox1.List = Ray
End Sub

So When i Select XYZ in comboRefNo.Text should display 3
if I Select ABC it should display 4 in comboRefNo.Text

With your code
Keeping Ref.No as Base with this Ref.Code, Trans.Code and Goods Descrpiton will be displayed in their respective Textboxes.

Pl see the Modified Structure for your reference

	A	B	C	D	E
1	Firm Name	Ref No	REF CODE	TRANS CODE	Goods Description
2	ABC	1	1	88	fdsfdssfdsfdsfds
3				23	dfdsdsfdsfds
4	ABC	2	2	1	fddsfdsdsffdsf
5				2	fdsfkdsjfhdskjfds fhdbfdskhfdsf
6				3	fdsfdfdsfdsfdfdds
7	XYZ	1	43	44	fffffff44ffffffff
8				55	55ggggggggg
9				23
10				77	yrtytytryrtryt
11				88	fdsfdssfdsfdsfds
12	ABC	3	55	88	fdsfdssfdsfdsfds
13	XYZ	2	2	1	fddsfdsdsffdsf
				2	fdsfkdsjfhdskjfds fhdbfdskhfdsf

<tbody>
</tbody>

Thanks
NimishK