Any way to make this MACRO faster??

[fisht]

Hi all,

Can someone please take a look and hopefully figure out a way to speed up the execution of the following vba code?

Thank you in advance!

Code:

Sub Remove_Rows()


Sheets("Data").Activate
Dim LRow As Long, n As Long

LRow = Range("C1000000").End(xlUp).Row
For n = LRow To 1 Step -1
If Cells(n, 3).Value = "n/a" Then Cells(n, 3).EntireRow.Delete
Next n


End Sub

 

[Smitty]

You can use AutoFilter, then Goto-->Special-->Visible cells only, then delete.

That's easily recordable, and it's a lot faster than looping.

HTH,

 

[fisht]

Hi Smitty-

I actually need the rows to be removed by using a macro and not by filtering.. any ideas?

You can use AutoFilter, then Goto-->Special-->Visible cells only, then delete.

That's easily recordable, and it's a lot faster than looping.

HTH,

 

[Smitty]

Like I said, it's easily recordable.

Feel free to post what you get back here and someone can set it up dynamically.

 

[fisht]

Smitty- sorry I am not quite following what you meant by "You can use AutoFilter, then Goto-->Special-->Visible cells only, then delete. That's easily recordable, and it's a lot faster than looping."

What would be the best way to 'record' this macro to delete the rows that only contain a "n/a" in column C?

Like I said, it's easily recordable.

Feel free to post what you get back here and someone can set it up dynamically.

 

[Smitty]

If you start recording a macro, you can apply AutoFilter to your range, then filter on n/a. From there you select the range and use Goto (F5)-->Special-->Visible cells only, which will select just the filtered n/a rows. Then you can delete the rows and just the n/a rows will be deleted. Then you can remove the filter and stop recording.

 

[fisht]

Thanks for your response Smitty. I will try that out!

If you start recording a macro, you can apply AutoFilter to your range, then filter on n/a. From there you select the range and use Goto (F5)-->Special-->Visible cells only, which will select just the filtered n/a rows. Then you can delete the rows and just the n/a rows will be deleted. Then you can remove the filter and stop recording.

 

[fisht]

Your proposed solution was much faster than the original macro, thank you!!

Thanks for your response Smitty. I will try that out!