Projekt-Anfragen: Tel: 07022/9319004 info@CodeDocu.de Software Entwicklung in C# WPF Asp.Net Core Vba Excel Word SQL-Server EF Linq, UWP Net
#

 

 

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

 

Mobile
»
Excel Code: in Powerpoint Grafiken oder Bereiche einfügen
»
Neu (22.03.20Excel fehler: -2147188160 LinkFormat.BreakLink : Invalid request. This operation requires a linked object.19 14:59:14)
»
Excel vba: Blätter in eine neue Datei kopieren
»
Excel Code für ein kleines Addin
»
Excel Checkbox in vba makro setzen
»
Excel vba makro: Anzeigen aller entsperrten Zellen
»
Excel vba: Verlinkungen löschen
»
Excel vba : Pivot Datenquelle aktualisieren
»
Excel vba: alle Namens-Variablen löschen
»
Gelöst: Excel vba: row ausblenden row.entirerow.hidden ist zu langsam

.

Jobs, Projekte, Angebote für Freiberufler, Selbstständig an Info@CodeDocu.de