Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1436to1440
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Realtiver Zellbereich in Makro

Realtiver Zellbereich in Makro
24.07.2015 14:48:38
Huffi
Hallo Excelfreunde,
ich habe folgende makro im Internet gefunden:

Sub Zeilen_einfügen_Aufmass()
Dim strBer
Dim myCell As Range
Dim checkCell As Boolean
checkCell = True
Select Case checkCell
Case Not Intersect(ActiveCell, Range("Bereich1")) Is Nothing: strBer = "Bereich1"
Case Not Intersect(ActiveCell, Range("Bereich2")) Is Nothing: strBer = "Bereich2"
Case Else: Exit Sub
End Select
ActiveSheet.Unprotect "Probe"
With ActiveCell.EntireRow
If Range(strBer).Rows.Count > 1 Then
.Copy
.Insert
Else
'MsgBox "Sie versuchen die letzte Zeile des bearbeitbaren Bereichs zu löschen!" &   _
_
vbCrLf & vbCrLf & "Der Vorgang wird abgebrochen!", vbExclamation + vbOKOnly
GoTo Abbruch
End If
End With
Abbruch:
ActiveSheet.Protect "Probe"
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub

Die Makros werden über Schaltflächen auf dem Blatt ausgeführt (Zeilen einfügen bzw. ausgewählte Zeile löschen)
Folgendes Problem hat sich ergeben.
Ich habe den Bereich1 und Bereich 2 definiert. Bereich1 geht von $B$31:$F$38 und wird absolut gesetzt.
Sobald ich eine Zeile über das makro einfüge, wird der Bereich um eine Zeile nach unten versetzt $B$32:$F$39.
Will ich nun die Zeile 31 löschen, befindet sie sich aus dem definierten Bereich und ich kann sie nicht löschen.
Will ich dann eine neue Zeile nach Zeile 31 einfügen, kann ich es auch nicht machen, weil sie nicht mehr im Bereich1 einhalten ist.
Wie muss ich den Bereich definieren, dass er sich automatisch vergößert (beim Einfügen von Zeilen) bzw. verekleiner, wenn ich eine Zeile lösche?
Habe versucht den Bereich so zu definieren: $B$31:$F38
Ergebnis: Excel hat mir sowohl Bereich1 als auch Bereich2 ($B$41:$F$46) zusammengefasst.
Ich hoffe, dass ich mich richtig ausgedrückt habe.
Danke für die Hilfe
Huffi
Das Beispiel liegt bei.
https://www.herber.de/bbs/user/99057.xls

4
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Realtiver Zellbereich in Makro
24.07.2015 16:14:34
Michael
Hallo Huffi,
zunächst habe ich Deine Makros aus "Diese Arbeitsmappe" nach "Tabelle1" verfrachtet.
Die CASE-Anweisung kommt mir nicht koscher vor; ich habe sie zumindest beim Zeilen einfügen umformuliert.
Probier das mal aus:
Sub Zeilen_einfügen()
' Hilfestellung von:
' http://stackoverflow.com/questions/11031891/how-do-i-redefine-a-named-range-with-vba
Dim strBer As String
Dim zeilen As Long
Dim bereich As Name
Dim ersteZeile As Boolean
If Not Intersect(ActiveCell, Range("Bereich1")) Is Nothing Then
strBer = "Bereich1"
Else
If Not Intersect(ActiveCell, Range("Bereich2")) Is Nothing Then
strBer = "Bereich2"
Else: Exit Sub
End If
End If
ersteZeile = False
If Range(strBer)(1).Row = ActiveCell.Row Then
ersteZeile = True
zeilen = Range(strBer).Rows.Count
Set bereich = ActiveWorkbook.Names.Item(strBer)
End If
ActiveSheet.Unprotect "Probe"
With ActiveCell.EntireRow
If Range(strBer).Rows.Count >= 1 Then
.Copy
.Insert
Else
GoTo Abbruch
End If
End With
If ersteZeile Then
With bereich
.RefersTo = .RefersToRange.Offset(-1, 0).Resize(zeilen + 1)
End With
End If
Abbruch:
ActiveSheet.Protect "Probe"
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub
Es wird überprüft, ob die aktive Zelle in der ersten Zeile des Bereichs liegt, und falls ja, wird der Bereich geändert.
Viel einfacher und ohne weitere Programmierung ist es *generell*, einen Bereich, der verformelt ist, jeweils oben und unten eine Zeile größer zu machen, wobei diese beiden Zeilen dann auf sehr geringe Höhe gezogen werden, damit niemand reinklickt (Du kannst sie ja eh schützen). Dann kann der Anwender (oder Dein Makro) Zeilen einfügen, löschen usw., ohne daß der Bereich (bzw. eine Formel) beeinträchtigt wird.
Kapiert?
Werte in A3 bis A5, in A7 die Formel =SUMME(A2:A6); die Zeilen 2 und 6 auf minimale Höhe setzen (oder ausblenden), und die Formel ist sicher vor Fehlern durch einfügen/löschen von Zeilen - Bereiche auch.
Schöne Grüße,
Michael

Anzeige
AW: Realtiver Zellbereich in Makro
27.07.2015 14:08:31
Huffi
Hallo Markus,
danke für Deine Hilfe. Habe Deinen Rat befolgt und es funktioniert sehr gut.
Habe folgenden Code für das Lösachen eingegeben und es funktioniert.
....
If Range(strBer).Rows.Count >= 1 Then
.Delete
Else
....
Ich hoffe, dass es passt und dass ich nicht noch auf andere Kleinigkeiten schauen muss.
Bin echt froh, dass Du Dir die Zeit genommen hast.
Danke nochmals
Huffi

freut mich, danke für die Rückmeldung
27.07.2015 16:39:20
Michael
Hi Huffi,
ich heiße zwar nicht Markus, macht aber nix.
Hm, hm, ich weiß nicht, ob das "=" beim Löschen sinnvoll ist: beim Einfügen isses ja ok...
Ich würde das noch testen und schlimmstenfalls das = wieder rausnehmen.
Happy Exceling,
Michael

Anzeige
AW: freut mich, danke für die Rückmeldung
28.07.2015 14:03:50
Huffi
Hallo Micheal,
ich habe das Makro getestet und keinen Unterschied gefunden, ob "=" dabei ist oder nicht.
Danke nochmal für die Hilfe
Viel Spaß beim Exceln ;-)
Huffi

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige