Readdy Write  
0,00 €
Your View Money
Views: Count
Self 20% 0
Your Content 60% 0

Users by Links 0
u1*(Content+Views) 10% 0
Follow-Follower 0
s2*(Income) 5% 0

Count
Followers 0
Login Register as User

07.03.2019 (👁4513)

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