Gelöst: Excel vba: row ausblenden row.entirerow.hidden ist zu langsam
Problem:
das Ausblenden von Zeilen in Microsoft Excel ist extrem langsam.
Lösung:
Mit folgenden vba Anweisungen kann man die Geschwindigkeit von Excel Zeilen ausblenden
'< speed > ws.DisplayPageBreaks = False Application.Calculation = xlManual Application.ScreenUpdating = False '*speed row ausblenden true->false Application.EnableEvents = False '< speed > |
Option Explicit '***********< Zeilen und Spalten anpassen >**************** '*Blendet Entwicklerspalten mit z oder dpi ein aus '* Public Sub Zeilen_Spalten_in_Arbeitsmappe_einausblenden(ByVal SetAnsicht As Boolean) '-----------------< Zeilen_Spalten_in_Arbeitsmappe_einausblenden() >----------------- '< active Workbook > Dim wb As Workbook Set wb = ActiveWorkbook '</ active Workbook >
Application.ScreenUpdating = True
'----< Sheets ermitteln >---- Dim ws As Worksheet
'--< @Loop: alle Sheets >-- For Each ws In wb.Sheets If ws.Range("A1").Value = "96dpi" Then Application.StatusBar = Now & " Zeilen ein ausblenden in " & ws.Name Zeilen_Spalten_auf_Blatt_einausblenden ws, SetAnsicht End If Next '--< @Loop: alle Sheets >--
Application.StatusBar = Now & " " & wb.Name & " fertig: Z einausblenden" '-----------------</ Zeilen_Spalten_in_Arbeitsmappe_einausblenden() >----------------- End Sub Public Sub Zeilen_Spalten_auf_Blatt_einausblenden(ByRef ws As Worksheet, ByVal SetAnsicht As Boolean) '-----------------< Zeilen_Spalten_auf_Blatt_einausblenden() >----------------- '*Blendet Spalten mit z ein aus '< speed > ws.DisplayPageBreaks = False Application.Calculation = xlManual Application.ScreenUpdating = False '*speed row ausblenden true->false Application.EnableEvents = False '< speed >
'----< Sheets ermitteln >---- '--< @Loop: alle Sheets >-- 'Set ws = wb.Worksheets(sSheetname) Dim varValue As Variant
'-< Columns >- Dim iCol As Integer For iCol = ws.UsedRange.Columns.Count To 1 Step -1 varValue = ws.Cells(1, iCol).Value If Not IsEmpty(varValue) Then If Not IsNumeric(varValue) Then
Dim col As Range Set col = ws.Columns(iCol)
varValue = LCase(varValue) Application.StatusBar = Now & " " & ws.Name & "." & iCol
If varValue Like "z" Or varValue Like "*dpi*" Then Application.StatusBar = Now & " " & ws.Name & ".z-col: " & iCol If SetAnsicht = False Then '---< Hide >---- If col.EntireColumn.Hidden <> True Then col.EntireColumn.Hidden = True 'DoEvents '---</ Hide >---- Else '---< Show >---- If col.EntireColumn.Hidden <> False Then col.EntireColumn.Hidden = False 'DoEvents '---</ Show >---- End If End If End If End If Next '-</ Columns >-
'-< Rows >- Dim iRow As Integer For iRow = ws.UsedRange.Rows.Count To 1 Step -1 varValue = ws.Cells(iRow, 1).Value
If Not IsEmpty(varValue) Then If Not IsNumeric(varValue) Then
Dim row As Range Set row = ws.Rows(iRow)
varValue = LCase(varValue) Application.StatusBar = Now & " " & ws.Name & "." & iRow
If varValue Like "z" Or varValue Like "*dpi*" Then Application.StatusBar = Now & " " & ws.Name & ".z-row:" & iRow If SetAnsicht = False Then '---< Hide >---- If row.EntireRow.Hidden <> True Then row.EntireRow.Hidden = True '---</ Hide >---- Else '---< Show >---- If row.EntireRow.Hidden <> False Then row.EntireRow.Hidden = False '---</ Show >---- End If End If End If End If Next '-</ Columns >- DoEvents Application.StatusBar = Now & " " & ws.Name & " fertig: Z einausgeblendet"
'< speed > ws.DisplayPageBreaks = True Application.Calculation = xlAutomatic Application.ScreenUpdating = True Application.EnableEvents = True '< speed > '-----------------</ Zeilen_Spalten_auf_Blatt_einausblenden() >----------------- End Sub |