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
[TABLE="class: grid, width: 500, align: right"]
<tbody>[TR]
[TD][TABLE="width: 0"]
<tbody>[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Firm Name[/TD]
[TD]REF CODE[/TD]
[TD]TRANS CODE[/TD]
[TD]Goods Description[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ABC[/TD]
[TD]1[/TD]
[TD]88[/TD]
[TD]fdsfdssfdsfdsfds[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]23[/TD]
[TD]dfdsdsfdsfds[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]XYZ[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]fddsfdsdsffdsf[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]fdsfkdsjfhdskjfds fhdbfdskhfdsf[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]fdsfdfdsfdsfdfdds[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]AAA[/TD]
[TD]43[/TD]
[TD]44[/TD]
[TD]fffffff44ffffffff[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD]55[/TD]
[TD]55ggggggggg[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]23[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD]77[/TD]
[TD]Yrtytytryrtryt[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD]88[/TD]
[TD]Fdsfdssfdsfdsfds[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]TMOT[/TD]
[TD]55[/TD]
[TD]88[/TD]
[TD]fdsfdssfdsfdsfds[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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

 

[NimishK]

I've resolved my post 10. Thanks

 

[Peter_SSs]

I've resolved my post 10. Thanks

Great.
Thanks for letting us know.

 

[NimishK]

Peter
Small Feedback as per your code i tried with 29 columns and 1st Single Entry infact showing the Individual Row Address From header row til the range

this what was observed and
The ComboBox1.List = Ray displayed the Header Row its Address From A1:AC1 then Next in the Combolist it displayed the Header of B1 and Address B1:AD1, C1 and C1:AE1 and so on......
uptil AC1:BE2 after this it showed A2:AC3 , B2:AD3 uptil AC3:BE54
I dont know why this Happened.

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("AC" & 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, 29).Address(0, 0)
  Next c
  ComboBox1.List = Ray
End Sub

Extremely sorry for the trouble

 

[Peter_SSs]

I don't have your setup, so I can't really replicate what is happening for you.

 

[NimishK]

Peter


Can you try your code with my post 1 Structure for 4 columns. Whether 4 columns or 29 columns the result observed as follows
Observed that data for ONLY 1st firm. i.e range A3:D4 only.
If the Data is blank ie from Range A4 onwards. Combobox1.list displayed the Header Row its Address From A1:D1 and thereafter it displayed address of individual rows.


I request you to kindly delete or remove the data from A4:D13 and see the results in combobox1

 

[NimishK]

I would like the best use of Combobox1_Click() event and combobox1_Change () Event

As per Structrue of worksheet as per post #10
As getting Range Address in comboBox1. So when Clicking in it how can i get a value in Textbox1 and Textbox2

Textbox1 for Eg of Cell C2 of range A2:E5
Textbox2 value oF Cell D2 of range A2:E5

When next item clicked on Combobox1
So Textbox1 should display of Cell C4 of range A4:E6
Textbox2 to display value of Cell D4 of range A4:E6

Clicked on 3rd item of combobox1 and so on
Textbox1 should display of Cell C7 of range A7:E11
Textbox2 to display value of Cell D7 of range A7:E11
and so on

and From Change_Event ()
is to type the Value of c.Value and getting the result as above

Got many errors type Mismatch, subscript out of range, Object Variable or with Block Variable not set, method range of object _global failed in Combobox1_Click event

 

[NimishK]

Peter
Sorry To bother
Hope this is the Last one.
I Require the syntax to get the respective Range Address in txtRangeaddress.Text while clicking on ComboBox1 though already displaying in comboBox1.List as per your Code.