0

I am trying to delete unfiltered/hidden rows from my excel using Macro vb script.

I have the below for loop to do this but it takes about 45 seconds to execute. There are only 72 rows in my excel.

Sub Exchange_Rates()
'
' Exchange_Rates Macro

    Dim count
    count = Application.Worksheets.count
    Worksheets(count).Activate
    Rows("1:3").EntireRow.Delete
    Selection.AutoFilter
    ActiveSheet.Range("A:BT").AutoFilter Field:=1, Criteria1:="USD"
    Dim LastRow

    LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.count).Row
    
    For i = LastRow To 1 Step -1
        If Rows(i).Hidden = True Then Rows(i).EntireRow.Delete
    Next
End Sub
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Priyank Ajmera
  • 1
  • 1
  • Just inspect and remove hidden rows. https://www.exceldome.com/solutions/delete-hidden-rows-and-columns-in-a-workbook/ – M-- Sep 01 '22 at 14:19
  • @M-- Thanks, but I want to write it in vb as I want to call it from a Batch file to do this repeatedly for multiple excels periodically. – Priyank Ajmera Sep 01 '22 at 14:21
  • If it's taking 45 seconds to delete a maximum of 72 rows, then something else is going on. I suspect turning off calculations and/or other events temporarily will speed this up significantly. – CLR Sep 01 '22 at 14:29
  • try these to see if you can make your code run faster: https://codereview.stackexchange.com/questions/159298/vba-syntax-that-compiles-sub-data-into-master-sheet/160569#160569 – M-- Sep 01 '22 at 14:50
  • Filter for cells that <> USD ,then delete the visible rows all at once. – Davesexcel Sep 01 '22 at 18:26

0 Answers0