Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Spaltenauswahl über Liste

Betrifft: Spaltenauswahl über Liste von: Daniel
Geschrieben am: 17.10.2020 08:42:23

Hallo,

im Archiv habe ich unter dem unten stehnenden Link ein VBA-Skript gefunden das es ermöglicht eine Spalte über ihre Überschrift zu finden. Was schon einmal eine tolle Sache ist.
Wie aber kann dem Skript eine Liste vorgeben werden, um dieses klein und übersichtlich zu halten, wenn man mehrere Spalten auswählen möchte?

Die beste Lösung sollte doch eine Liste und for-Schleife sein. Leider habe ich in VBA noch nie mit Listen und for-Schleifen gearbeitet. Wäre nett wenn mir jemand aushelfen könnte.
Ich bedanke mich im Vorfeld für jede Unterstützung.

Viele Grüße
Daniel

https://www.herber.de/forum/archiv/1292to1296/1294201_Spalten_mit_UeberschriftenNamen_ansprechen.html

'Skript-Ersteller Klaus M.vdT.

Sub MarkiereSpalte()
On Error GoTo hell
Dim sText As String
Dim iCol As Integer
Dim lRow As Long

'nach dieser Überschrift suchen!
sText = "description"

'in dieser Zeile stehen die Überschriften!
lRow = 1

iCol = Application.WorksheetFunction.Match(sText, Cells(lRow, 1).EntireRow, False)
Cells(1, iCol).EntireColumn.Select

GoTo heaven
hell:
MsgBox ("Überschrift " & sText & " nicht vorhanden!")

heaven:

'Skript-Ersteller Klaus M.vdT.
End Sub

Betrifft: AW: Spaltenauswahl über Liste
von: Hajo_Zi
Geschrieben am: 17.10.2020 08:46:57

schreibe für
Cells(lRow, 1).EntireRow
Range("A1:B1")



Zu Deiner Datei kann ich nichts schreiben, was wohl daran liegt das ich nicht auf fremde Rechner schaue.Ich baue keine Datei nach.

Sollte die Datei verlinkt werden?

Wenn du an Stelle einer Demomappe deine Originalmappe hochladen willst, diese aber sensible Daten enthält, kannst du diese Daten
http://www.ms-office-forum.de/forum/showthread.php?t=322895
änderrn.

Bilder lade ich mir nicht runter, da Excel damit nichts anfangen kann.

http://www.excel-ist-sexy.de/bilder-statt-datei/

Hochgeladene Bilder können zwar als solche in Excel importiert werden, sind jedoch bei der Lösung von Problemen nicht sehr hilfreich, da man die eigentlichen Daten nicht ohne große und zeitraubende Umwege direkt in die Tabelle übertragen kann.

Das ist nur meine Meinung zu dem Thema.

GrußformelHomepage

Betrifft: AW: Spaltenauswahl über Liste
von: Daniel
Geschrieben am: 17.10.2020 09:56:21

Hallo Hajo,

vielen Dank das du mir zur Seite stehst.

Ich habe deinen Rat beherzigt und eine Demomappe erstellt (die Originaldaten stehen gerade nicht zur Verfügung da ich nicht Zuhause bin)
https://www.herber.de/bbs/user/140925.xlsx

Bearbeitet werden soll ein Output von einem Programm der mehrere Spalten umfasst, die vom Namen (erster Wert der Spalte) her immer gleich sind aber die Positionen sind ändern kann.

Ich weiß die Namen der Spalten die ich ausgeblendet/gelöscht haben will und ich habe den das Skript von Klaus M.vdT. mit dem ich ich eine Spalte über die Überschrift finde und auswählen kann.

Wie kann diese Funktion nun auf eine vorhandene Liste angewendet werden?



Liste der Spalten die ausgeblendet werden sollen:
"2 - vorerst nicht benötigt","9 - vorerst nicht benötigt","10 - vorerst nicht benötigt","12 - vorerst nicht benötigt","13 - vorerst nicht benötigt"

Liste der Spalten die gelöscht werden sollen:
"4 - irrelevant Spalte","7 - irrelevant Spalte","11 - irrelevant Spalte"


Ich hoffe das ich mein Vorhaben nun besser verdeutlichen konnte.

Betrifft: AW: Spaltenauswahl über Liste
von: Werner
Geschrieben am: 17.10.2020 13:27:35

Hallo Daniel,

deine "Liste" mit den Spaltennamen die ausgeblendet/gelöscht werden sollen auf ein gesondertes Tabellenblatt "Tabelle2".
Die Spaltenüberschriften die ausgeblendet werden sollen in Spalte A (untereinander) beginnend ab A1.
Die Spaltenüberschriften die gelöscht werden sollen in Spalte B (untereinander) beginnend ab B1.

Code:
Option Explicit

Public Sub Ausblenden_Löschen()
Dim i As Long, z As Long, raAusblenden As Range
Dim raLöschen As Range, wsListe As Worksheet

Application.ScreenUpdating = False
Set wsListe = ThisWorkbook.Worksheets("Tabelle2")

With Worksheets("Tabelle1")
    For i = 1 To .Cells(1, .Columns.Count).End(xlToLeft).Column
        For z = 1 To wsListe.Cells(wsListe.Cells.Rows.Count, "A").End(xlUp).Row
            If .Cells(1, i) = wsListe.Cells(z, "A") Then
                If raAusblenden Is Nothing Then
                    Set raAusblenden = .Cells(1, i)
                Else
                    Set raAusblenden = Union(raAusblenden, .Cells(1, i))
                End If
            End If
        Next z
    Next i
    If Not raAusblenden Is Nothing Then
        raAusblenden.EntireColumn.Hidden = True
    End If
    
    For i = 1 To .Cells(1, .Columns.Count).End(xlToLeft).Column
        For z = 1 To wsListe.Cells(wsListe.Cells.Rows.Count, "B").End(xlUp).Row
            If .Cells(1, i) = wsListe.Cells(z, "B") Then
                If raLöschen Is Nothing Then
                    Set raLöschen = .Cells(1, i)
                Else
                    Set raLöschen = Union(raLöschen, .Cells(1, i))
                End If
            End If
        Next z
    Next i
    If Not raLöschen Is Nothing Then
        raLöschen.EntireColumn.Delete
    End If
End With

Set wsListe = Nothing: Set raAusblenden = Nothing: Set raLöschen = Nothing
End Sub
Gruß Werner

Betrifft: AW: Spaltenauswahl über Liste
von: Daniel
Geschrieben am: 17.10.2020 14:20:47

Hallo Werner,

auch Dir danke ich für deine Antwort!
Deine Funktion läuft einwandfrei, genau so soll das Ergebnis aussehen.

Dürfte ich Dich aber noch Fragen wie ich meine Listen direkt in den Code einarbeiten kann?
Sonst muss ich, so wie eben beim Testen, immer manuell eine Tabelle mit meinen Listen anlegen.
Dann wäre es alles aus einem Guss und eine 100%ige Automatisierung.

Gruß Daniel

Betrifft: AW: Spaltenauswahl über Liste
von: Werner
Geschrieben am: 17.10.2020 14:33:20

Hallo,

das wolltest du doch so!!

Das ist so auch wesentlich einfacher zu "pflegen".

Wenn irgendwann mal weitere "Überschriften", die ausgeblendet oder gelöscht werden sollen, dazukommen oder wegfallen, dann brauchst du nur deine Listen im Blatt anzupassen.

Halst du die Überschriften direkt im Code, dann mußt du bei irgenwelchen Änderungen dauernd den Code anpassen.

Gruß Werner

Betrifft: AW: Spaltenauswahl über Liste
von: Daniel
Geschrieben am: 17.10.2020 15:09:02

Hallo Werner,

meine abschließende Frage sollte keine Bemängelung an deinem Code sein!
Wie gesagt gibt es absolut nichts zu meckern am Ergebnis. Das ist Perfekt!

Und Du liegst natürlich richtig damit, dass ein rüberspringen in "Tabelle2", Werte ergänzen/löschen
am flottesten geht.

Aber da die zu bearbeitende Datei wie gesagt ein Outputfile eines Programmes ist und
jedes Mal neu geschrieben wird, müsste auch jedes Mal "Tabelle2" angelegt und befüllt werden.

Dadurch das sich die Spalten nicht bei jedem Bearbeiten ändern wäre ein Sprung ins VBA vertretbar.

Was mir jetzt erst während dem Schreiben aufgefallen ist:
Weil ich die Listen in die Tabelle eingefügt habe, bin ich wieder voll ins Fettnäpfchen getreten *facepalm*

Diese waren als Vorbereitung gedacht weil ich davon ausgegangen bin das sie als Liste im Code angelegt werden. Deshalb waren die Werte direkt in "" gesetzt.

Hätte dazuschreiben müssen. Sorry Werner.

Betrifft: AW: Spaltenauswahl über Liste
von: Werner
Geschrieben am: 17.10.2020 17:11:45

Hallo,

dann beispielsweise so:
Option Explicit

Public Sub Ausblenden_Löschen()
Dim i As Long, raAusblenden As Range, raLöschen As Range

Application.ScreenUpdating = False

With Worksheets("Tabelle1")
    For i = 1 To .Cells(1, .Columns.Count).End(xlToLeft).Column
        Select Case .Cells(1, i)
            Case "2 - vorerst nicht benötigt", "9 - vorerst nicht benötigt", _
            "10 - vorerst nicht benötigt", "12 - vorerst nicht benötigt", _
            "13 - vorerst nicht benötigt"
                If raAusblenden Is Nothing Then
                    Set raAusblenden = .Cells(1, i)
                Else
                    Set raAusblenden = Union(raAusblenden, .Cells(1, i))
                End If
            Case Else
        End Select
    Next i
    If Not raAusblenden Is Nothing Then
        raAusblenden.EntireColumn.Hidden = True
    End If
    
    For i = 1 To .Cells(1, .Columns.Count).End(xlToLeft).Column
        Select Case .Cells(1, i)
            Case "4 - irrelevant Spalte", "7 - irrelevant Spalte", "11 - irrelevant Spalte"
                If raLöschen Is Nothing Then
                    Set raLöschen = .Cells(1, i)
                Else
                    Set raLöschen = Union(raLöschen, .Cells(1, i))
                End If
            Case Else
        End Select
    Next i
    If Not raLöschen Is Nothing Then
        raLöschen.EntireColumn.Delete
    End If
End With

Set raAusblenden = Nothing: Set raLöschen = Nothing
End Sub
Gruß Werner

Betrifft: AW: Spaltenauswahl über Liste
von: Daniel
Geschrieben am: 17.10.2020 17:28:26

Hi
Vielleicht so, wenn die Spaltenüberschrifen direkt in Code hinterlegt werden sollen:
dim C as Range
For each C in active sheet.usedrange. rows(1).cells
    Select Case C.Text
        Case "A", "B", "C"
            C.EntireColumn.delete
        Case "E", "F", "G"
             C.EntireColumn.Hidden = True
        Case Else
    End Select
Next
BTW, die Lists mit den Spalten für Werners Vorschlag kann auch in der Datei hinterlegt werden, die das Bearbeitungsmakro enthält.
Dann muss sie nicht neu generiert werden, sonden ist zusammen mit dem Makro vorhanden

Gruß Daniel

Betrifft: AW: Spaltenauswahl über Liste
von: Daniel
Geschrieben am: 17.10.2020 18:27:19

Hallo Werner, hallo Daniel,

1000 Dank euch beiden! Das erleichter meinen Vorgang, bei meiner Tabelle mit über 50 Spalten, ungemein.
Das Macro wird nun bei den persönlichen Macros gespeichert und Dank euch nur bei Bedarf angepasst.

Ich wünsche euch noch ein schönes Wochenende!

Viele Grüße
Daniel

Betrifft: Gerne u. Danke für die Rückmeldung. o.w.T.
von: Werner
Geschrieben am: 17.10.2020 19:31:21