Problem mit Excel: Scrollbalken wieder willen

  • #1
A

ArcaneLion

Bekanntes Mitglied
Themenersteller
Dabei seit
28.03.2004
Beiträge
371
Reaktionspunkte
0
Hoi!

Folgendes Prbolem in Excel 2002:

Wenn man eine Tabelle anlegt und in Zeile 1000 (als Beispiel) runterscrollt und danach wieder hochscrollt, passt sich der Scrollbalken automatisch wieder an.

Wenn man alldings etwas in die Zelle einträgt, den Eintrag wieder löscht un dann wieder hochscrollt, bleibt der Scrollbalken so klein...
Auch das markieren des Bereichs und Inhalte löschen oder Zellen löschen habt nicht geholfen

Kennt da wer ne Abhilfe?
 
  • #2
Gibt keine direkte Abhilfe.
Excel merkt sich die unterste bisher in diesem Tabellenblatt beschriebene Zeile und die rechteste Spalte. Und zwar in absoluten Werten. Das heißt, daß auch das Löschen oder Verschieben dieser Zeilen/Spalten nix hilft.
Wenn du aus Versehen mal Zeile 65.000 beschrieben hast und es dich nervt, daß Strg+Ende immer in den Orcus führt, mußt du den relevanten Bereich deiner Tabelle mit Strg+C und Strg+V in ein neues Datenblatt kopieren und das versaute Original löschen. Formeln und Zellbezüge werden automatisch mitkopiert.
 
  • #3
doch ;)

z.B. mit diesem Makro
Code: 
Option Explicit
Sub LeereZellenAufEmptySetzen()
  Dim zelle As Range
  For Each zelle In ActiveSheet.UsedRange
   ->kein Inhalt/Formel enthalten ?
    If zelle.Value =  And zelle.FormulaR1C1 =  Then
      zelle.Value = Empty
    End If
  Next
End Sub

Gruß Matjes ;D
 
  • #4
...was machst du eigentlich dne ganzen Tag lang wenn du nicht irgentwelche Makros für mich schreibst!?

Wenn du denkst in Excel gehts nicht mehr, kommt irgentwo ein Matjes-Makro her!

Danke!
 
  • #5
Bei mir zittert der Cursor ein wenig, aber mit Strg+Ende lande ich danach immer noch in der Pampa. Wo liegt der Storch im Salat?
 
  • #6
Hallo klexy,

hab noch etwas herumprobiert und dabei festgestellt, dass sich excel anscheinend auch die stellen merkt, wo Formatierungen und Rahmen angelegt und wieder gelöscht wurden - also nicht nur Formeln und Werte.

Deshalb hier eine neue Version, die (hoffentlich) auch dieses bereinigt.

Es wird die letzte Zeile bestimmt, in der eine Formel oder ein Wert enthalten ist. Dann werden ab der darauffolgenden Zeile->jungfräuliche' Zeilen aus dem noch nicht genutzen Bereich bis zur Strg+E-Position kopiert.

Einfach mal an einer Kopie ausprobieren und Wirkung berichten.

(bei 65530 Zeilen hat der Makro bei mir mehrere Minuten gebraucht - also nicht unruhig werden ;D )

Gruß Matjes :)

Code: 
Option Explicit
Public Sub LeereZellenAufEmptySetzen2()
  Dim l_LastRow As Long, l_LastCol As Long
  Dim l_RealLastRow As Long, l_c As Long, l_r As Long
  Dim l_rCnt As Long, l_rCopy As Long, l_rAnz As Long
  Dim ws As Worksheet, zelle As Range
  
  Set ws = ActiveSheet
  l_LastRow = ActiveSheet.UsedRange.Rows.Count
  l_LastCol = ActiveSheet.UsedRange.Columns.Count
  
  l_RealLastRow = 1->default: nix auf dem Blatt
  For l_r = l_LastRow To 2 Step -1
    For l_c = 1 To l_LastCol
      If ws.Cells(l_r, l_c).Value <>  Or _
          ws.Cells(l_r, l_c).FormulaR1C1 <>  Then
       ->letzte Zeile mit Inhalt oder Formel merken
        l_RealLastRow = l_r
      End If
    Next
    If l_RealLastRow <> 1 Then Exit For
  Next
    
 ->Bereich der bereinigt werden muß (l_r + 1) bis l_LastRow
  If (l_r) < l_LastRow Then
   ->Anzahl Zeilen, die bereinigt werden müssen
    l_rCnt = l_LastRow - l_r
   ->einen sauberen Bereich nach l_LastRow kopieren
    If 65536 - l_LastRow < 2 Then
      MsgBox (Es kann keine Bereinigung stattfinden, & vbLf & _
      da keine jungfräulichen Zeilen mehr vorhanden sind :-( )
      Exit Sub
    Else
     ->Anzahl der kopierten jungfräulichen Zeilen
     ->möglichst groß wählen
      If 65536 - (l_LastRow + 1) < l_rCnt Then
        l_rAnz = ((65536 - (l_LastRow + 1)) \ 2) + 1
      Else
        l_rAnz = l_rCnt
      End If
    End If
    ws.Rows((l_LastRow + 1) & : & (l_LastRow + l_rAnz)).Copy
   ->erste Zeile für Paste
    l_rCopy = l_r + 1
    Do While l_rCnt > 0
     ->einen sauberen Bereich kopieren (nach l_LastRow)
      ws.Rows((l_LastRow + 1) & : & (l_LastRow + l_rAnz)).Copy
      ws.Cells(l_rCopy, 1).Select
      ws.Paste
      l_rCopy = l_rCopy + l_rAnz
      l_rCnt = l_rCnt - l_rAnz
    Loop
    Application.CutCopyMode = False
    
    For Each zelle In ActiveSheet.UsedRange
     ->kein Inhalt/Formel enthalten ?
      If zelle.Value =  And zelle.FormulaR1C1 =  Then
        zelle.Value = Empty
      End If
    Next
  End If
  ActiveCell.SpecialCells(xlLastCell).Select
  Set ws = Nothing
End Sub
 
  • #7
Matjes schrieb:
(bei 65530 Zeilen hat der Makro bei mir mehrere Minuten gebraucht - also nicht unruhig werden ;D )
Zum Vergrößern anklicken....

Ja, am tollen Bürorechner hats auch nur 83 Minuten gedauert...naja...habe nen gutes Buch aufm PocketPC und werde nach Stunden bezahlt ;D
 
  • #8
Boah ey, ganz schön dickes Ding, das Makro. ;)
Es ist zwar schön ausführlich kommentiert, aber immer noch eine Nummer (oder fünf) zu hoch für mich. Vielleicht liegt es auch an der Zeit...

Das Makro funktioniert fast immer aber ...
Ich schick dir eine Mail mit meinem Problemfall, wo die überflüssigen Zeilen geleert werden, nicht aber die überflüssigen Spalten.
 
  • #9
Hallo klexy,

jetzt eine Version mit löschen der Spalten/Zeilesn ausserhalb des benutzten Bereichs.

Gruß Matjes :)
Code: 
Option Explicit
Sub ScrollBalkenAufBenutztenBereichReduzieren()
'alle Spalten und Zeilen ausserhalb des benutzten Bereichs werden gelöscht.
'Der benutzte Bereich ist der Bereich,der Formeln oder Werte enthält

  Dim l_LastRow As Long, l_LastCol As Long
  Dim l_LastRowFormel As Long, l_LastColFormel As Long
  Dim l_LastRowWert As Long, l_LastColWert As Long
  Dim l_RealLastRow As Long, l_RealLastCol As Long
  Dim l_c As Long, l_r As Long, c As Long
  Dim ws As Worksheet
  
  Set ws = ActiveSheet
  
 ->Zeile/Spalte des angeblich benutzten Bereichs
  l_LastRow = ActiveSheet.UsedRange.Rows.Count
  l_LastCol = ActiveSheet.UsedRange.Columns.Count
  
 ->Zeile/Spalte der letzten Formel bestimmen
  Call ZeileSpalte_letzteFormel(ws, l_LastRowFormel, l_LastColFormel)
  
 ->Zeile/Spalte des letzten Wertes bestimmen
  Call ZeileSpalte_letzterWert(ws, l_LastRowWert, l_LastColWert)
  
  l_RealLastRow = Application.WorksheetFunction _
                  .Max(l_LastRowFormel, l_LastRowWert)
  l_RealLastCol = Application.WorksheetFunction _
                  .Max(l_LastColFormel, l_LastColWert)
  
  ws.Rows(l_RealLastRow + 1 & : & l_LastRow).Delete
  For c = l_LastCol To l_RealLastCol + 1 Step -1
    ws.Columns(c).EntireColumn.Delete
  Next
  ws.Cells(1, 1).Select
  ws.Cells(l_RealLastRow, l_RealLastCol).Select
 ->notwendig, damit Strg+Ende richtig reagiert
  l_LastRow = ActiveSheet.UsedRange.Rows.Count
  l_LastCol = ActiveSheet.UsedRange.Columns.Count
  
End Sub

'*************************************************************
Private Sub ZeileSpalte_letzteFormel( _
            ws As Worksheet, Zeile As Long, Spalte As Long)
 ->bestimmt die Zeile und Spalte der letzten Formel
 ->auf dem Arbeitsblatt
  Dim zelle As Range, l_r As Long, l_c As Long
  Zeile = 0: Spalte = 0
  On Error GoTo ErrorHandler
  For Each zelle In ws.Cells.SpecialCells(xlCellTypeFormulas)
   ->adr = zelle.Address
    If Zeile < zelle.Row Then Zeile = zelle.Row
    If Spalte < zelle.Column Then Spalte = zelle.Column
  Next
 ->Zeile = ws.Range(adr).Row: Spalte = ws.Range(adr).Column
  Exit Sub
ErrorHandler:
  Err.Clear->keine Formel vorhanden
End Sub
'*************************************************************
Private Sub ZeileSpalte_letzterWert(ws As Worksheet, _
                          Zeile As Long, Spalte As Long)
 ->bestimmt die Zeile und Spalte des letzten Wertes
 ->auf dem Arbeitsblatt
  Dim l_r As Long, l_c As Long, l_LastRow As Long, l_LastCol As Long
  l_LastRow = ActiveSheet.UsedRange.Rows.Count
  l_LastCol = ActiveSheet.UsedRange.Columns.Count
  
  Zeile = 0: Spalte = 0
  For l_c = 1 To l_LastCol
    l_r = ws.Cells(ws.Rows.Count, l_c).End(xlUp).Row
   ->größte Zeilennummer merken
    If Zeile < l_r Then
      If l_r > 1 Then
        Zeile = l_r
      ElseIf ws.Cells(l_r, l_c).Value <>  Then Zeile = l_r
      End If
    End If
   ->Spalte merken, wenn nicht leer
    If l_r > 1 Or ws.Cells(l_r, l_c).Value <>  Then Spalte = l_c
  Next
End Sub
 
  • #10
Einwandfrei!
;D ;D ;D
 
  • #11
Hi zusammen,

klexy hat noch einen Bug gefunden. Wenn alles bereits i.O.ist, wird die letzte Zeile mit Inhalt gelöscht. -Mist :mad:

Dank an klexy :D

Gruß Matjes :)

Hier die korrigierte Version:
Code: 
Option Explicit
Sub ScrollBalkenAufBenutztenBereichReduzieren()
'alle Spalten und Zeilen ausserhalb des benutzten Bereichs werden gelöscht.
'Der benutzte Bereich ist der Bereich,der Formeln oder Werte enthält

  Dim l_LastRow As Long, l_LastCol As Long
  Dim l_LastRowFormel As Long, l_LastColFormel As Long
  Dim l_LastRowWert As Long, l_LastColWert As Long
  Dim l_RealLastRow As Long, l_RealLastCol As Long
  Dim l_c As Long, l_r As Long, c As Long
  Dim ws As Worksheet
  
  Set ws = ActiveSheet
  
 ->Zeile/Spalte des angeblich benutzten Bereichs
  l_LastRow = ActiveSheet.UsedRange.Rows.Count
  l_LastCol = ActiveSheet.UsedRange.Columns.Count
  
 ->Zeile/Spalte der letzten Formel bestimmen
  Call ZeileSpalte_letzteFormel(ws, l_LastRowFormel, l_LastColFormel)
  
 ->Zeile/Spalte des letzten Wertes bestimmen
  Call ZeileSpalte_letzterWert(ws, l_LastRowWert, l_LastColWert)
  
  l_RealLastRow = Application.WorksheetFunction _
                  .Max(l_LastRowFormel, l_LastRowWert)
  l_RealLastCol = Application.WorksheetFunction _
                  .Max(l_LastColFormel, l_LastColWert)
  
  
 ->auf tatsächlich benutzte Zeilen reduzieren
 ->Korrektur von klexy :-) nur löschen, wenn nicht alles i.O. ist
  If l_RealLastRow <> l_LastRow Then
    ws.Rows(l_RealLastRow + 1 & : & l_LastRow).Delete
  End If
 ->auf tatsächlich benutzte Spalten reduzieren
  For c = l_LastCol To l_RealLastCol + 1 Step -1
    ws.Columns(c).EntireColumn.Delete
  Next
  
  ws.Cells(1, 1).Select
  ws.Cells(l_RealLastRow, l_RealLastCol).Select
 ->notwendig, damit Strg+Ende richtig reagiert
  l_LastRow = ActiveSheet.UsedRange.Rows.Count
  l_LastCol = ActiveSheet.UsedRange.Columns.Count
  
End Sub

'*************************************************************
Private Sub ZeileSpalte_letzteFormel( _
            ws As Worksheet, Zeile As Long, Spalte As Long)
 ->bestimmt die Zeile und Spalte der letzten Formel
 ->auf dem Arbeitsblatt
  Dim zelle As Range, l_r As Long, l_c As Long
  Zeile = 0: Spalte = 0
  On Error GoTo ErrorHandler
  For Each zelle In ws.Cells.SpecialCells(xlCellTypeFormulas)
   ->adr = zelle.Address
    If Zeile < zelle.Row Then Zeile = zelle.Row
    If Spalte < zelle.Column Then Spalte = zelle.Column
  Next
 ->Zeile = ws.Range(adr).Row: Spalte = ws.Range(adr).Column
  Exit Sub
ErrorHandler:
  Err.Clear->keine Formel vorhanden
End Sub
'*************************************************************
Private Sub ZeileSpalte_letzterWert(ws As Worksheet, _
                          Zeile As Long, Spalte As Long)
 ->bestimmt die Zeile und Spalte des letzten Wertes
 ->auf dem Arbeitsblatt
  Dim l_r As Long, l_c As Long, l_LastRow As Long, l_LastCol As Long
  l_LastRow = ActiveSheet.UsedRange.Rows.Count
  l_LastCol = ActiveSheet.UsedRange.Columns.Count
  
  Zeile = 0: Spalte = 0
  For l_c = 1 To l_LastCol
    l_r = ws.Cells(ws.Rows.Count, l_c).End(xlUp).Row
   ->größte Zeilennummer merken
    If Zeile < l_r Then
      If l_r > 1 Then
        Zeile = l_r
      ElseIf ws.Cells(l_r, l_c).Value <>  Then Zeile = l_r
      End If
    End If
   ->Spalte merken, wenn nicht leer
    If l_r > 1 Or ws.Cells(l_r, l_c).Value <>  Then Spalte = l_c
  Next
End Sub
 
  • #12
man dankt!
 
  • #13
matjes, super makro, besten Dank! Das bringt eine Riesenerleichterung, auch für meine Emailsattachments...
 
  • #14
Ah, hier scheint sich jemand auszukennen...
Ich hab dasselbe Problem. Teilweise hilft das Makro aus. Nur in einem gewissen Bereich gibts keine Änderung, aus welchen Gründen auch immer. :'(
Ich würde gerne ein Beispiel posten, das geht aber leider anscheinend nicht bei euch... Problem ist bei mir nicht, daß der Scrollbalken komische Verenkungen macht, sondern das die Datei so ewig groß wird. Was kann man außer Zellen löschen denn noch löschen?!
 
  • #15
Ich glaube das Problem bei mir ist der Zellenschutz: Bei den betroffenen Zellen ist der Zellenschutz deaktiviert, obwohl er ja standardmäßig aktiviert ist.
Wenn ich den Zellenschutz jetzt wieder aktiviere, hat das allerdings keinen Effekt, die Zelle scheint noch immer als benutzt deklariert zu sein. Kennt jemand eine Möglichkiet, Zellen komplett neu zu initialisieren?
 
  • #16
Geht das nicht auch ohne Makros? Also ich mach das dann immer so, dass ich alle Zeilen, in denen nichts stehen sollte lösche und die ganze Datei nochmal speicher. dann zieht sich der Scrollbalken automatsch wieder in die richtige position.

LG
 
Thema:

Problem mit Excel: Scrollbalken wieder willen

ANGEBOTE & SPONSOREN

Neueste Themen

Statistik des Forums

Themen
113.840
Beiträge
707.963
Mitglieder
51.494
Neuestes Mitglied
Flensburg45
Oben