title: Efficiently delete all hidden columns and rows in a worksheet
body: <p>To delete all hidden columns and rows in a worksheet I am using:</p>
  
  <pre><code> With activeworkbook.Sheets(1)
  
             LR = LRow(activeworkbook.Sheets(1)) ' will retrieve last row no in the sheet
             lc = LCol(activeworkbook.Sheets(1)) ' will retrieve last column no in the sheet
  
              For lp = lc To 1 Step -1    'loop through all columns
                  If .Columns(lp).EntireColumn.Hidden = True Then .Columns(lp).EntireColumn.Delete
              Next lp
  
              For lp = LR To 1 Step -1    'loop through all rows
                  If .Rows(lp).EntireRow.Hidden = True Then .Rows(lp).EntireRow.Delete
              Next
  end with
  </code></pre>
  
  <p>But it takes a very long time as I have more than 300 columns and 1,000 rows. When I tried to estimate the total time required for the above operations, I found the following lines took most time:</p>
  
  <pre><code>For lp = lc To 1 Step -1    'loop through all columns
      If .Columns(lp).EntireColumn.Hidden = True Then _
           .Columns(lp).EntireColumn.Delete
  Next lp
  </code></pre>
  
  <p>but the next loop is much faster.  </p>
  
  <p>Do you have any suggestions to improve the execution speed?</p>
  
  <p>The code for LRow and LCol functions is below and I confirm it returns the correct last row and last column:</p>
  
  <pre><code>Function LRow(sh As Worksheet)
      On Error Resume Next
      LRow = sh.Cells.Find(What:="*", _
                              After:=sh.Range("A1"), _
                              Lookat:=xlPart, _
                              LookIn:=xlFormulas, _
                              SearchOrder:=xlByRows, _
                              SearchDirection:=xlPrevious, _
                              matchCase:=False).Row
      On Error GoTo 0
  End Function
  
  
  Function LCol(sh As Worksheet)
      On Error Resume Next
      LCol = sh.Cells.Find(What:="*", _
                              After:=sh.Range("A1"), _
                              Lookat:=xlPart, _
                              LookIn:=xlFormulas, _
                              SearchOrder:=xlByColumns, _
                              SearchDirection:=xlPrevious, _
                              matchCase:=False).Column
      On Error GoTo 0
  End Function
  </code></pre>
  
  <p>I was looking at using .specialcells for selecting all visible columns, and reverse it for deletion.  </p>