-1

This is a very basic question, however, I have not been able to find a solution online thus far. I have a macro that processes a large amount of data and usually takes about 2-3 seconds to run. However, I hide some of the cell it references to protect those cells from being edited by other users and now the macro takes 5-10 minutes to run. Any idea as to why this occurs and how to circumvent it? Thanks in advance! 

'Delete previous data from the same month

Sheet3.Select
intValueToFind = Sheet8.Range("K6")
Range("A2").Select
Selection.AutoFilter
ActiveSheet.UsedRange.Select
Selection.AutoFilter field:=9, Criteria1:=intValueToFind

Selection.Offset(1, 0).Select

lr = Cells(Rows.Count, 1).End(xlUp).Row
If lr > 1 Then
    Range("a2:A" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End If

The issue begins around the Selection.Autofilter line.

TDJ
  • 121
  • 1
  • 14
  • 8
    Kind of hard to say without seeing the code. Can you please [edit](https://stackoverflow.com/posts/51249222/edit) you question with it? – cybernetic.nomad Jul 09 '18 at 15:37
  • 2
    Unfortunately this isn't a common problem (fast before hiding cells, slow after) which is probably why you haven't found anything searching online. Its definitely something that can be fixed in your code, but we can't help since it isn't shared here. – JNevill Jul 09 '18 at 15:44
  • 2
    @TDJ: Please read [How to create a Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve) before adding your code – cybernetic.nomad Jul 09 '18 at 15:51
  • 1
    Does your code use `Cells(x,y).Activate` in a big loop perhaps? I've noticed that activating cells that are hidden is still possible but take longer than if they're visible. – CLR Jul 09 '18 at 15:58
  • @cybernetic.nomad Thank you, here is the respective code. – TDJ Jul 09 '18 at 16:07
  • 4
    1) Read and apply the concepts in [avoid select in VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) 2) If it's causing an issue, unhide the cells in the code, then preform the code, then hide them again. (you can even make them a named range so all you have to do is hide / unhide that named range. – Scott Holtzman Jul 09 '18 at 16:14
  • @ScottHoltzman Thank you, this was the best way to get around it! – TDJ Jul 09 '18 at 18:49

1 Answers1

0

I have seen the same issue with I ran macros with large amounts of data and for some reason the hidden cells slow down the process considerably. I would try to hide the cells and then unhide them in your function as mentioned above.