[SOLVED] I am looking for a way to shorten this code by consolidating each "Case is" instant into one getting the case name from a variable

I am looking for a way to shorten this code by consolidating each "Case is" instant into one getting the case name from a variable

The purpose of the macro is to choose the correct data depending on the selected case in a dropdown menue on my "Recommendation" sheet.

This is my code:

Private Sub cmbNatCoList_Change()

'Define selected NatCo and variables for loop
Dim NatCoList As String
Dim ZeileUntersucht As Integer
Dim ZeileEintragen As Integer

'Line which is looked at in NatCo sheet
ZeileUntersucht = 17
'Line which is written in in Recommendation Sheet
ZeileEintragen = 15

'Selected NatCo
NatCoList = cmbNatCoList.Value

'Set selected NatCo on NatCoRecommendations sheet
'ThisWorkbook.Sheets("NatCoRecommendations").cmbNatCoList.Value = NatCoList

'Set position and size of combobox
cmbNatCoList.Top = 85
cmbNatCoList.Left = 1120
cmbNatCoList.Width = 100
cmbNatCoList.Height = 40

'Write selected NatCo on charts
Range("z1") = NatCoList
Worksheets("Stock Control Chart").Range("C61") = NatCoList
Worksheets("Price Chart").Range("D44") = NatCoList

'Search for recommendations for selected NatCo
 Select Case NatCoList

'========================================
Case Is = "TDG"
Worksheets("Recommendations").Rows("1:224").Hidden = False
    'Loop looking for recommendations in each line, writes BNCode in list if yes 
and continues with next line, if not just continues
For ZeileUntersucht = 20 To 515
    If Worksheets("TDG").Cells(ZeileUntersucht, 238).Value = "yes" Then
        Worksheets("Recommendations").Cells(ZeileEintragen, 3) = Worksheets("TDG").Cells(ZeileUntersucht, 1)
        ZeileEintragen = ZeileEintragen + 1

    End If
Next ZeileUntersucht




'===========================================
Case Is = "TMNL"
Worksheets("Recommendations").Rows("1:263").Hidden = False
    For ZeileUntersucht = 20 To 515
        If Worksheets("TMNL").Cells(ZeileUntersucht, 238).Value = "yes" Then
            Worksheets("Recommendations").Cells(ZeileEintragen, 3) = 
Worksheets("TMNL").Cells(ZeileUntersucht, 1)
            ZeileEintragen = ZeileEintragen + 1

    End If
Next ZeileUntersucht

This is parts of the code I am trying to improve.

Those Case Is cases are about 20 in total I am just showing you two here. The code inside is exactly the same, the only thing that changes with a selected case is the worksheet it is looking at.

Important to note: It is always only one case selected (used) for the macro.

If you need any more information, please ask.

Solution

I've noticed that the number of rows that in Recommendations that you set Hidden to false has changed for the first Case is 1:224 and the second is 1:263, but if this supposed to be the same then something like the following would do, simply replace your whole Select Case with the following:

Worksheets("Recommendations").Rows("1:224").Hidden = False
    For ZeileUntersucht = 20 To 515
        If Worksheets(NatCoList).Cells(ZeileUntersucht, 238).Value = "yes" Then
            Worksheets("Recommendations").Cells(ZeileEintragen, 3) = Worksheets(NatCoList).Cells(ZeileUntersucht, 1)
            ZeileEintragen = ZeileEintragen + 1
        End If
    Next ZeileUntersucht