getting the data in between the cells dynamically

Very simple change


While Not rec.EOF
   i = i + 1
   Tabelle2.Cells(i, 1) = (rec.Fields("ver_nr1").Value)
   Tabelle2.Cells(i, 2) = (rec.Fields("ver_bes1").Value)
    i = i + 1                  'this makes room for the values you want to enter
   Tabelle2.Cells(i, 1) = (rec.Fields("New_Field_Here").Value)
   Tabelle2.Cells(i, 2) = (rec.Fields("New_Field_Here").Value)

   rec.MoveNext
Wend

how can i group them dynamically and also give them color, i mean to the rows

What do you mean group them?  show an example of how you them to display

For the colour select the column you want to color like this

Tabelle2.Cells(i, 1).select

Then color it like this

    With Selection.Interior
        .ColorIndex = 6
        .Pattern = xlSolid
    End With

by grouping i mean doing this control automatically

top menu - Data - group and outline - group

i = i + 1
   Tabelle2.Cells(i, 1) = (rec.Fields("ver_nr1").Value)
   Tabelle2.Cells(i, 2) = (rec.Fields("ver_bes1").Value)
i = i + 1                  'this makes room for the values you want to enter
   Tabelle2.Cells(i, 1) = (rec.Fields("New_Field_Here").Value)
   Tabelle2.Cells(i, 2) = (rec.Fields("New_Field_Here").Value)

leaving the above two i want to colour the entire row

OK.  Here

    Range("A23:A33").Select
    Selection.Rows.Group

If you don't know where the range ends, the value of variable i after the while loop ends will contain the last row we put data in, so you could do

    Range("A23:A" & i).Select
    Selection.Rows.Group

To color entire row

Tabelle2.Cells(i, 1).entirerow.select

    With Selection.Interior
        .ColorIndex = 6             'This is yellow, what color do you want
        .Pattern = xlSolid
    End With

>>>'This is yellow, what color do you want
       
i want light blue

when im selecting a new one its throwing error at this line >>>Selection.Rows.Group
as the group methd of the range object cannot be called

All the blur colors I can see are
8,5,28,42,34,33,32

Try these numbers one at a time to see which one suits you best.  Just change ColorIndex

ColorIndex = yournumberhere

and also the grouping is remaining, how can i make a new grouping with each selection, and when nothing is selected then no grouping shld be visible

I don't understand this.  what do you mean "with each selection".  Do you mean the user selects a row, a column or several rows maybe.  Explain a bit more.  It is not really easy to imagine what you have in mind without verbose explanation

oops sorry

well im having two comboboxes, one us having order number and other is having suborder numbers, combining both im taking as a value and retreiving data from a view, so each sub order has different data, so this data i want to display in the format of

ver_nr1      desc

stn_nr      details        year
stn_nr      details        year

ver_nr2      desc

stn_nr      details        year
stn_nr      details        year

and to these data i want to colour and group data depending on ver_nr
so ver_nr1 will become one group and 2 will become another group

well how can i delete or take out grouping before a new group is made

sorry was out for some time

thanks a lot

just one more thing, how can i take out colouring??

with first one some cells are coloured and with the second one, the old one is remaining

i want the cells to be coloured only till the values are there

    With Selection.Interior
        .ColorIndex = xlColorIndexNone              
        .Pattern = xlSolid
    End With

You need to use the help as all this info is in th help file.  Make sure you have VBA help installed with Excel (it doesn't install by default) then just highligh a word a press F1.  For example if you highlight ColorIndex and press F1 it will tell you the color values

>>>> With Selection.Interior
        .ColorIndex = xlColorIndexNone              
        .Pattern = xlSolid
    End With

i mean with every selection, old values and old colour shld go away and new values and colour should come

i tried using help and also some macros, but they arent so helpful

Can you post what the code look like now and put comment on where you want the colors be removed

my code is something like this, but im not able to clear the contents and colour with every new selection, i dont know where im going wrong


Private Sub CommandButton3_Click()

Dim clk As String
Dim i As Integer

clk = ComboBox1.List(ComboBox1.ListIndex) + "." + ComboBox2.List(ComboBox2.ListIndex)

MsgBox clk

Dim conn As ADODB.Connection
Dim rec As ADODB.Recordset


Set conn = New ADODB.Connection

conn.Open "Driver={Microsoft ODBC for Oracle};" & _
                   "Server=;" & _
                   "Uid=;" & _
                   "Pwd=;"

   Set rec = New ADODB.Recordset

rec.Open "SELECT * FROM project_data3 where ang_kost1='" & clk & "'", conn
i = 22

ActiveCell.Offset(1, 0).Range("A1").Select
Range("A24:A" & i).Select
Selection.ClearContents
Selection.ClearOutline

 Tabelle2.Cells(i, 1) = (rec.Fields("ver_nr1").Value)
  Tabelle2.Cells(i, 2) = (rec.Fields("ver_bes1").Value)

While Not rec.EOF

   i = i + 1
   Tabelle2.Cells(i, 1) = (rec.Fields("stn_nr1").Value)
   Tabelle2.Cells(i, 3) = (rec.Fields("ua_nr1").Value)
   Tabelle2.Cells(i, 4) = (rec.Fields("psp1").Value)
   Tabelle2.Cells(i, 6) = (rec.Fields("prod_bes1").Value)
   Tabelle2.Cells(i, 7) = (rec.Fields("ve1").Value)
   
   

Range("A24:A" & i).Select
Selection.ClearOutline
Selection.Rows.Group


 Tabelle2.Cells(i, 1).EntireRow.Select

    With Selection.Interior
        .ColorIndex = 8
        .Pattern = xlSolid
         
    End With
   
 
   rec.MoveNext
Wend






rec.Close
Set rec = Nothing

End Sub

with every new click i want to display new contents and colour, i mean only the rows with contents shld be coloured and

If there are no other data on the sheet use this

Private Sub ComboBox1_Click()
    Tabelle2.UsedRange.Select
    Selection.Clear
    Selection.ClearFormats
End Sub

If there other data then you must clear only the range you want

Private Sub ComboBox1_Click()
    Tabelle2.range("Put your range here").Select
    Selection.Clear
    Selection.ClearFormats
End Sub

is my question clear????

if you see my code, i just want to replace the range, for eg:-

when im selecting one value from combobox and pressing button, im displaying valies on the cells dynamically, and these cells are having the values and are coloured, now when i select a new one, im able to clear the values and put the new ones but im not able to clear the colour,

what i want is the rows are coloured only till the values are there, so everytime i need to clear the colour and colour it new

I thinnk it is now.  I will post in 5 to 10 mins I am in the middle of something now

ok

Try this

After the line

MsgBox clk

add this

    Rows("22:" & i).Select
    Selection.Interior.ColorIndex = xlNone
    Selection.ClearContents

Rows("22:" & i).Select
    Selection.Interior.ColorIndex = xlNone
    Selection.ClearContents


im getting error as type incompatible

Sorry.  You already have code that clears content like this

Range("A24:A" & i).Select
Selection.ClearContents
Selection.ClearOutline

add this line after that code
Selection.Interior.ColorIndex = xlNone

If you still get the error replace the line I gave above with
Selection.Interior.ColorIndex = xlColorIndexNone

If it is not covering the range you want then adjust the 24 to something else 22 or 23

I am off to lunch, back in an hour or so

i have tried both, its not clearing the colour

................

Tabelle2.Range("a23:a500").Interior.ColorIndex = 2
Tabelle2.Range("b23:b500").Interior.ColorIndex = 2
Tabelle2.Range("c23:c500").Interior.ColorIndex = 2

i dont know right or wrong, im doing something like this and its working,

Put a breakpoint on the line after
MsgBox clk

Then tell me the value of i at that point - do msgbox i

Also where I said this
Selection.Interior.ColorIndex = xlNone

instead try
Selection.ClearFormats

Private Sub CommandButton3_Click()

Dim clk As String
Dim i As Integer

clk = ComboBox1.List(ComboBox1.ListIndex) + "." + ComboBox2.List(ComboBox2.ListIndex)

MsgBox clk
MsgBox i

value of i is 0

Stupid me!!!!!

Change
Dim i As Integer

to
Static i As Integer

the value of i is changing according to the last cell, but the colour is not going

i mean i selected one value and it coloured 20 cells and i showed value 20 and
i selected another value it had around 15 values, and the remaining 15 - 20 cells are still having colour

Please post code again as it is now from beginning up to this line

While Not rec.EOF

heres my complete code

Private Sub CommandButton3_Click()

Dim clk As String
Static i As Integer

clk = ComboBox1.List(ComboBox1.ListIndex) + "." + ComboBox2.List(ComboBox2.ListIndex)

MsgBox clk
'MsgBox i

Dim conn As ADODB.Connection
Dim rec As ADODB.Recordset

Set conn = New ADODB.Connection

conn.Open "Driver={Microsoft ODBC for Oracle};" & _
                   "Server=;" & _
                   "Uid=;" & _
                   "Pwd=;"

   Set rec = New ADODB.Recordset

rec.Open "SELECT * FROM project_data3 where ang_kost1='" & clk & "'ORDER BY stn_nr1 ASC", conn
i = 22

ActiveCell.Offset(1, 0).Range("A1").Select
Call clear_rows
Range("A24:A" & i).Select
Selection.ClearContents
Selection.ClearOutline
Selection.ClearFormats


While Not rec.EOF
Tabelle2.Range("b22") = (rec.Fields("ver_nr1").Value)
Tabelle2.Range("e22") = (rec.Fields("ver_bes1").Value)
   i = i + 1
   Tabelle2.Cells(i, 1) = (rec.Fields("stn_nr1").Value)
   Tabelle2.Cells(i, 3) = (rec.Fields("ua_nr1").Value)
   Tabelle2.Cells(i, 4) = (rec.Fields("psp1").Value)
   Tabelle2.Cells(i, 6) = (rec.Fields("prod_bes1").Value)
   Tabelle2.Cells(i, 7) = (rec.Fields("ve1").Value)
   Tabelle2.Cells(i, 8) = (rec.Fields("ve1").Value)
 
Range("A24:A" & i).Select
Selection.ClearOutline
Selection.Rows.Group
Selection.HorizontalAlignment = xlCenter
Selection.Font.Bold = True

 Tabelle2.Cells(i, 1).EntireRow.Select

    With Selection.Interior
        .ColorIndex = 8
        .Pattern = xlSolid
         
    End With
   
    rec.MoveNext
Wend



rec.Close
Set rec = Nothing

This block of code

Range("A24:A" & i).Select
Selection.ClearContents
Selection.ClearOutline
Selection.ClearFormats

Should go after
msgbox clk

Do that and let me know what you get

its working only for the first A column

OK

Change this
Range("A24:A" & i).Select
to
Range("A24:D" & i).Select

ok its working.......thanks a lot

Cool.  Please if you need another help with this open a new question.  I helped as much as I can on this one

ya ill open a new question,

thanks a lot again

Hello  gbzhhu

could you please refre to this new post, thanks

https://www.experts-exchange.com/questions/21397231/displaying-data-in-a-distinct-format-in-excel-sheet.html