Adresse der 1. Zelle in einem Bereich per VBA ermitteln

Question

Hallo,

ich habe einen Bereich mit einem Namen versehen (z.B. "Quelle", $A$1:$B$5).
Jetzt möchte ich per VBA die Adresse der oberen linken Zelle ermitteln - und zwar unter "Nutzung" meines Bereiches "Quelle".

Kann mir da jemand helfen?

Vielen Dank, Gruß
Jojo

Answer 1

Hallo JoJo,

probier mal das

MsgBox Chr(64 + Range("Quelle").Column) & Range("Quelle").Row

Gruß
Rainer

Answer 2

Hallo Jo,

vielleicht geht es auch einfacher?
Option Explicit

Sub JoJo()
' Name BereichTest
MsgBox Mid(ActiveWorkbook.Names.Item("BereichTest"), InStr(ActiveWorkbook.Names.Item("BereichTest"), "!") + 1, InStr(ActiveWorkbook.Names.Item("BereichTest"), ":") - InStr(ActiveWorkbook.Names.Item("BereichTest"), "!") - 1)
End Sub

Gruß Hajo

Answer 3

Hallo,

vielen Dank euch beiden. Die Adresse hätte ich jetzt. Mit dieser will ich weiter arbeiten - und zwar davon ausgehend meine Schlüssel für eine Sortierung festlegen.
Im Endeffekt möchte ich einen Bereich sortieren - und sowohl die Adresse des Bereichs, als auch die Sortierschlüssel vor Fremdeinwirkungen wie Einfügen oder Löschen von Spalten/Zeilen schützen.
Oder gibt es dafür eine ganz andere - vielleicht einfachere - Lösung?

Gruß
Jojo

Answer 4

Hi Jojo,

die Adresse der ersten Zelle eines mittels Namen definierten Bereichs kann man auch einfacher ermitteln: Range("Quelle").Cells(1).Address

Deine Frage ist unklar - was meinst du mit "schützen"? Der einfachste Weg, einen Bereich unabhängig von seiner konkreten Zelladresse zu machen, ist die Definition von Bereichsnamen (was du ja schon gemacht hast)

Bis später,
Karin

Answer 5

Hallo Karin,

danke - mit "schützen" meine ich folgendes:
Anhand des Bereichsnamens bleibe ich diesbzgl. flexibel. Aber als Sortierkriterium wird - bspw. beim Aufzeichnen - immer eine konkrete Zelle angegeben. Und die möchte ich auch flexibel gestalten. D.h. falls eine Spalte "A" eingefügt wird, soll das Sortierkriterium nicht "B20" bleiben, sondern dann - korrekterweise - "C20" sein.

Gruß
Jojo

Answer 6

Hi Jojo,

dann ist dein Problem ja gelöst - mit dem definierten Namen für den Bereich und der Adresse für die linke obere Zelle des Bereichs. Oder sehe ich das falsch?

Bis später,
Karin

Answer 7

Hallo Karin,

gelöst ja - aber ich weiß nicht, ob das ganz korrekt ist. Hier mein Code:

With ThisWorkbook.ActiveSheet

Set raZelle = .Range("Quelle")
ErsteZeile = .Range(raZelle.Address).Row
ErsteSpalte = .Range(raZelle.Address).Column
myKey1 = Cells(ErsteZeile, ErsteSpalte + 1).Address
myKey2 = Cells(ErsteZeile, ErsteSpalte).Address

.Range("Quelle").Sort Key1:=Range(myKey1), Order1:=xlAscending, Key1:=Range(myKey2), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal

End With

Ich dachte, "myKey1" und "myKey2" müssten Ranges sein, aber wenn ich diese innerhalb der Klammer in Anführungszeichen schreibe, bekomme ich einen Fehler. Jetzt hab' ich die Klammern weggelassen, es funktioniert, aber ich weiß nicht recht, warum ...

Kannst Du mir das erklären?

Danke, Gruß
Jojo

Answer 8

Hi Jojo,

myKey1 und myKey2 sind in deinem Code Zell-Adressen, also ganz ordinäre Strings. Das siehst du, wenn du den Code im Einzelschrittmodus durchläufst und den Cusor mal auf die Variablen hältst.

Wenn du es mit Ranges machen willst, dann so:

Sub sortieren()
Dim myKey1 As Range
Dim myKey2 As Range
With ThisWorkbook.ActiveSheet
Set myKey1 = .Range("Quelle").Cells(2)
Set myKey2 = .Range("Quelle").Cells(1)
.Range("Quelle").Sort Key1:=myKey1, Order1:=xlAscending, Key1:=myKey2, Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
End With
End Sub

oder ganz einfach:

Sub sortieren()
With ThisWorkbook.ActiveSheet
.Range("Quelle").Sort Key1:=.Range("Quelle").Cells(2), Order1:=xlAscending, Key1:=.Range("Quelle").Cells(1), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
End With
End Sub

Bis später,
Karin

Answer 9

Hallo Karin,

danke für die tolle Hilfe. Dieses Forum ist echt spitze - speziell von Dir hab' ich ja schon sehr viel Unterstützung erhalten :-)))

Viiielen Dank und noch einen schönen Tag
Jojo