Auswahl invertieren
Marc Honervogt
ich bereite mit ein paar Makros EXCEL-Dateien für den anschließenden Import
in ACCESS auf. Dabei stoße ich auf folgendes Problem:
Ich markiere mit einem Makro alle Zeilen mit Inhalt. Jetzt möchte ich mit
einem Befehl diese Auswahl invertieren (also alle leeren Zeilen markieren)
um auf diese Auswahl einmal einen Lösch-Befehl auszuführen da ACCESS sonst
sämtliche Zeilen des Arbeitsblattes (also ca. 65.000) importiert da in den
leeren Zeilen während der Aufbereitung Daten gestanden haben.
Wie also per Makro die Auswahl invertieren.
Vielen Dank für eure Hilfe
Marc
Wolf W. Radzinski
Marc Honervogt schrieb in Nachricht <9ldpgj$qui$1...@news.mch.sbs.de>...
>Wie also per Makro die Auswahl invertieren.
das hab ich ja noch nie gehört? eine Selection kann grundsätzlich aus
beliebig vielen nicht zusammenhängenden Zellbereichen bestehen ... und
von einer automatischen Invertierung hab ich noch nie was gehört -
gibt's die?
ich hätte da nur 'ne mehr oder weniger langsame VBA Eigenbaulösung:
Sub invert_select()
Dim c As Range
Dim r As Range
Dim old As String
'in oldr wird die alte Selection abgelegt
oldr = Selection.Address
newr = ""
ober = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count
For Each r In Rows
If r.Row >= ober Then GoTo not_used
'wenn die Spalte mit der alten Selection keine gemeinsame Zelle hat
'füge ganze Spalte per UNION zur neuen Selection hinzu
If Intersect(r, Range(oldr)) Is Nothing Then
If newr <> "" Then
newr = Union(Range(newr), r).Address
Else
newr = r.Address
End If
Else
'hat die Spalte gemeinsame Zellen, dann mach die
'gleiche Prüfung für jede ZELLE der Spalte und füge
'die Zelle wenn nötig zur neuen Selection hinzu
For Each c In r.Cells
If Intersect(c, Range(oldr)) Is Nothing Then
If newr <> "" Then
newr = Union(Range(newr), c).Address
Else
newr = c.Address
End If
End If
Next c
End If
Next r
not_used:
'füge den gesamten Bereich oberhalb von Usedrange hinzu
If ActiveSheet.UsedRange.Rows.Count < 65536 Then
newr = Union(Range(newr), Rows(ober & ":65536")).Address
End If
Range(newr).Select
MsgBox Selection.Address
End Sub
Wolf W. Radzinski
hier die (hoffentlich) etwas schnellere Version:
Sub invert_select()
Dim c As Range
Dim r As Range
Dim old As String
oldr = Selection.Address
newr = ""
ober = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count
rechts = ActiveSheet.UsedRange.Column +
ActiveSheet.UsedRange.Columns.Count
For Each r In Rows
If r.Row >= ober Then GoTo not_used_r
If Intersect(r, Range(oldr)) Is Nothing Then
If newr <> "" Then
newr = Union(Range(newr), r).Address
Else
newr = r.Address
End If
Else
For Each c In r.Cells
If c.Column >= rechts Then GoTo not_used_c
If Intersect(c, Range(oldr)) Is Nothing Then
If newr <> "" Then
newr = Union(Range(newr), c).Address
Else
newr = c.Address
End If
End If
Next c
not_used_c:
If rechts < 256 Then
x = Range(Cells(r.Row, rechts), Cells(r.Row, 256)).Address
newr = Union(Range(newr), Range(x)).Address
End If
End If
Next r
not_used_r:
If ober < 65536 Then
Frank Arendt-Theilen
Folgende Aufgabenstellung:
Es soll die Differenzmenge zwischen der Spalte A und der UsedRange
ermittelt werden!
MfG Frank
_____________________________________________________
Frank Arendt-Theilen, Microsoft MVP für Excel, Hameln
E-Mail: Thei...@t-online.de
Am Wed, 15 Aug 2001 18:34:22 +0200, schrieb "Wolf W. Radzinski"
<Wolf.W.R...@onlinehome.de> in microsoft.public.de.excel zu "Re:
Auswahl invertieren":
Wolf W. Radzinski
Frank Arendt-Theilen schrieb in Nachricht ...
>Hallo Wolf,
>Folgende Aufgabenstellung:
>Es soll die Differenzmenge zwischen der Spalte A und der UsedRange
>ermittelt werden!
1) ich weiß daß der Code nicht besonders gut bis ziemlich schlecht ist
2) was hat UsedRange mit Selection zu tun? Leider kann eine Selection
weit über den UsedRange hinausgehen ... und die Inverse der Selection
ist das GANZE Blatt MINUS die Selection
3) wenn ich Spalte A selektiere, dann ist die INVERSE Dazu Spalte B:IV
die UsedRange spielt in dem Fall keine Rolle (die könnte ja z.B. nur aus
Zelle $A$1 bestehen ... ich hab versucht mit UsedRange die Bestimmung
der Inversen zu beschleunigen ... na ja ... ich weiß ... is nich ganz
gelungen.
4) Fällt dir dazu was besseres ein? Selektiere einfach mal ein paar
ganze Spalten und ein paar Zellbereiche und bilde davon die INVERSE
SELECTION - es geht, aber auf meine Art dauert es EWIG ohne und manchmal
auch mit Nutzung von UsedRange :-(
Thomas Ramel
Marc Honervogt schrieb:
>
> Ich markiere mit einem Makro alle Zeilen mit Inhalt. Jetzt möchte ich mit
> einem Befehl diese Auswahl invertieren (also alle leeren Zeilen markieren)
> um auf diese Auswahl einmal einen Lösch-Befehl auszuführen da ACCESS sonst
> sämtliche Zeilen des Arbeitsblattes (also ca. 65.000) importiert da in den
> leeren Zeilen während der Aufbereitung Daten gestanden haben.
>
> Wie also per Makro die Auswahl invertieren.
Wie Du aus den Postings entnehmen kannst, ist die Problemlösung nicht
gerade einfach ;-)
Wie wäre es, da Du ja schon mit VBA arbeitest, wenn du stattdessen
einfach die _leeren_ Zellen markieren würdest?
Schlimmstenfalls ist das ja "nur" eine Schleife mehr in deiner Prozedur
und Du löst einige Probleme damit!
BTW: Wie markierst Du die Zeilen mit Ihnalt? kannst Du den Code dazu
posten? Ev. gibt es eine minimale Anpassung für dein Problem!
--
Mit freundlichen Grüssen
Thomas Ramel
Schlesinger Konstruktionen
http://www.4853.ch/Schlesinger
Wolf W. Radzinski
Thomas Ramel schrieb in Nachricht ...
>Wie wäre es, da Du ja schon mit VBA arbeitest, wenn du stattdessen
>einfach die _leeren_ Zellen markieren würdest?
>Schlimmstenfalls ist das ja "nur" eine Schleife mehr in deiner Prozedur
>und Du löst einige Probleme damit!
>
>BTW: Wie markierst Du die Zeilen mit Ihnalt? kannst Du den Code dazu
>posten? Ev. gibt es eine minimale Anpassung für dein Problem!
genau ... so ähnlich war auch mein erster Gedanke :-) ... trotzdem wollt
ich mal wissen OB ES ÜBERHAUPT möglich ist ... aber 1) ist es nicht so
einfach und 2) gibt's die Funktion wohl deshalb nicht in Excel, weil die
Anzahl der Inversen Zellen meist RIESIG wäre und sowas verschlingt nur
unnütz Platz und Rechenzeit
Wolf W. Radzinski
siehe http://www.rendar.de
im Excel Verzeichnis die Datei "invert_selection.bas"
OHNE UsedRange! Es wird einzig die Selection genommen und die INVERSE
davon (begl. Arbeitsblatt) gebildet.
-wr-
Frank Arendt-Theilen
super Leistung von dir. Diese Funktion suchte ich schon etwas länger.
Ich habe mir erlaubt deine Funktion wie folgt etwas zu verändern ;-)
Option Base 1
Sub AuswahlInvertieren()
Dim arrAreas() As String
Dim lngI As Long
Dim rngBereich As Range
ReDim arrAreas(Selection.Areas.Count)
For lngI = 1 To UBound(arrAreas)
arrAreas(lngI) = Selection.Areas(lngI).Address
Next
Range(invers_selection(Range(arrAreas(1)))).Select
For lngI = 2 To UBound(arrAreas)
Intersect(Selection, _
Range(invers_selection(Range(arrAreas(lngI))))).Select
Next
End Sub
Private Function invers_selection(act_select As Range) As String
'On Error Resume Next
Dim part1 As Range
Dim part2 As Range
Dim part3 As Range
Dim part4 As Range
Dim p As Integer
p = 0
If act_select.Row > 1 Then
Set part1 = Rows("1:" & act_select.Row - 1)
p = 1
End If
If act_select.Row + act_select.Rows.Count - 1 < 65536 Then
Set part2 = Rows(act_select.Row + act_select.Rows.Count & ":65536")
p = p + 2
End If
If act_select.Column > 1 Then
Set part3 = Range(Columns(1), Columns(act_select.Column - 1))
p = p + 4
End If
If act_select.Column + act_select.Columns.Count - 1 < 256 Then
Set part4 = Range(Columns(act_select.Column + _
act_select.Columns.Count), Columns(256))
p = p + 8
End If
invers_selection = "$A$1"
Do While p > 0
Select Case p
Case 1, 3, 5, 7, 9, 11, 13, 15:
If invers_selection = "" Then
invers_selection = part1.Address
Else
invers_selection = Union(Range(invers_selection), part1).Address
End If
p = p - 1
Case 2, 3, 6, 7, 10, 11, 14, 15:
If invers_selection = "" Then
invers_selection = part2.Address
Else
invers_selection = Union(Range(invers_selection), part2).Address
End If
p = p - 2
Case 4, 5, 6, 7, 12, 13, 14, 15:
If invers_selection = "" Then
invers_selection = part3.Address
Else
invers_selection = Union(Range(invers_selection), part3).Address
End If
p = p - 4
Case 8, 9, 10, 11, 12, 13, 14, 15:
If invers_selection = "" Then
invers_selection = part4.Address
Else
invers_selection = Union(Range(invers_selection), part4).Address
End If
p = p - 8
End Select
Loop
End Function
MfG Frank
_____________________________________________________
Frank Arendt-Theilen, Microsoft MVP für Excel, Hameln
E-Mail: Thei...@t-online.de
Am Thu, 16 Aug 2001 00:27:20 +0200, schrieb "Wolf W. Radzinski"
<Wolf.W.R...@onlinehome.de> in microsoft.public.de.excel zu "und
es GEHT DOCH war(Re: Auswahl invertieren)":
Wolf W. Radzinski
Frank Arendt-Theilen schrieb in Nachricht ...
>super Leistung von dir.
Danke danke :-)
zwei drei Anmerkungen:
1)
On Error GoTo err_select <<<< muß sein!
Range(invers_selection(Range(arrAreas(1)))).Select
For lngI = 2 To UBound(arrAreas)
Intersect(Selection, _
Range(invers_selection(Range(arrAreas(lngI))))).Select
Next
Exit Sub
err_select: <<<< darf nicht fehlen!
ActiveCell.Select
End Sub
Grund! ist die Selektion das GANZE Arbeitsblatt, dann ist die INVERSE
LEER und führt zum Absturz!
2)
invers_selection = "" <<<< ? "$A$1" verfälscht die Auswahl, denn A1
muß nicht unbedingt Teil der INVERSEN Selektion sein!
und die Case Anweisungen waren bei mir in der ersten Version zu lang!
Do While p > 0
Select Case p
Case 1, 3, 5, 7, 9, 11, 13, 15:
Case 2, 6, 10, 14:
Case 4, 12:
Case 8:
3) man merkt, daß du die Variablendefinition SEHR genau nimmst - find
ich gut - da hab ich noch Nachholbedarf :-)
ich hab deine und meine Korrekturen jetzt zusammengelegt und in
invert_selection2a.bas auf meine HP geladen
4) diese Funktionalität hat nicht nur Dir schon lang gefehlt, auch ich
hab mich das ein oder andere Mal darüber geärgert, daß ich nicht per
Tastendruck die Auswahl invertieren konnte.
Da muß man Marc dafür danken, daß er danach gefragt hat :-)
Gruß -wr-
Frank Arendt-Theilen
vielen Dank für die Blumen!
Zu den beiden Punkten:
Beide Anweisungen wurden von mir gerade für den Spezialfall der
gesamtem Auswahl eingetragen.
zu 1) Statt bei Fehlereintritt ActiveCell.Select lasse ich jetzt eine
Msgbox mit entsprechender Info anzeigen. Es findet daher kein inverse
Selektion statt, also
On Error GoTo Fehler
...
Fehler:
MsgBox "Es wurde das ganze Tabellenblatt ausgewählt." & vbLf & _
"Hierfür gibt es keine inverse Auswahl!", vbOKOnly
zu 2) Aufgrund 1) kann ich wieder setzen: invers_selection = ""
Die Überlegungen hinsichtlich der Case-Überprüfungen sind prima und
wurden von mir auch gleich übernommen.
Unsere InversFunktion unterscheiden sich dann nur noch in der
Fehlerbehandlung.
MfG Frank
_____________________________________________________
Frank Arendt-Theilen, Microsoft MVP für Excel, Hameln
E-Mail: Thei...@t-online.de
Am Thu, 16 Aug 2001 16:22:37 +0200, schrieb "Wolf W. Radzinski"
<Wolf.W.R...@onlinehome.de> in microsoft.public.de.excel zu "Re:
und es GEHT DOCH war(Re: Auswahl invertieren)":
>
Wolf W. Radzinski
Frank Arendt-Theilen schrieb in Nachricht ...
>Unsere InversFunktion unterscheiden sich dann nur noch in der
>Fehlerbehandlung.
Hallo Frank,
jetzt sind die Unterschiede wieder etwas größer, denn nach ein paar
weiteren Tests hab ich entschieden, daß ich rngBereich wieder verwende
(du hast die Variable zwar erhalten, nutzt sie aber nicht!) Ich hab
nämlich festgestellt, daß meine frühere Wahl doch besser war - im ersten
Moment scheint es umständlich zu sein, aber auf den zweiten Blick war
mein Code schneller
set rngBereich = "irgendeinRange"
ist SCHNELLER als
"irgendeinRange".SELECT
und zwar ziemlich häufig "select" :-( ... ich bleibe lieber bei SET
rngBereich ... und rufe NUR EINMAL zum Schluß rngBereich.Select auf ...
probiers aus ... SET ist minimal SCHNELLER als x-mal SELECT! (vielleicht
liegt das aber auch am ScreenUpdating?)
bzgl Fehlerbehandlung ... da bleib ich auch bei Activecell, denn wenn
"normalerweise" in einem Sheet nichts selektiert wurde, dann ist
Activecell "auf alle Fälle" selektiert, d.h. die Inverse zum GANZEN
BLATT ist NICHTS (und der Cursor steht auf der aktiven Zelle - die
natürlich immer selektiert ist)
Bsp: bilde z.B. die Inverse zur Zelle $C$8 ... dann setzte mit <STRG>
und Maus den Cursor in Zelle $C$8 (d.h. nun sind alle Zellen
selektiert!) ... bilde erneut die Inverse ... der Cursor steht in $C$8
und keine weitere Zelle ist selektiert. Ich find das so o.k. ... jeder
wie er gern mag :-)
Gruß -wr-