Can anyone speed this macro up?

[justanotheruser]

Hi guys,

I've created this macro that will search down the rows in a sheet and delete all rows from A2 downwards if it has a "#" in Column A. However, each time it is run, it is looking down 10,000 rows - so it is taking a very long time to run!

Sub DeleteRowWithContents1()
'========================================================================
' DELETES ALL ROWS FROM A2 DOWNWARDS WITH THE WORDs "#"IN COLUMN A
'========================================================================
    Last = Cells(Rows.Count, "A").End(xlUp).Row
    For i = Last To 1 Step -1
        If (Cells(i, "A").Value) = "#" Then
            Cells(i, "A").EntireRow.Delete
        End If
    Next i
End Sub

Is there any way to make this work faster? One idea I had was to find all the rows that don't have a # in Column A (a "#" will be the only value of the cell if the row is to be deleted) and copy and paste those into a new sheet - is this possible, as I'd imagine it'd be a lot faster?

Excel Workbook
	A	B	C
1	scenario_code	company_code	account_code
2	#	#	#
3	#	#	#
4	#	#	#
5	#	#	#
6	#	#	#
7	#	#	#
8	#	#	#
9	#	#	#
10	#	#	#
11	#	#	#
12	2011_ACT	SSGUK	100100
13	2011_ACT	SSGUK	100100
14	2011_ACT	SSGUK	100100
15	2011_ACT	SSGUK	100100
16	2011_ACT	SSGUK	100100
17	#	#	#
18	#	#	#
19	#	#	#
ETL ICO

Thanks in advance for your help!

 

[Desu Nota from Columbus]

Add this to your code

Sub macroname()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

' Your code here...

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

 

[GlennUK]

I think you'll find AutoFiltering a lot faster. Filter to show the rows with "#" and then delete the rows in one go.

    Last = Cells(Rows.Count, "A").End(xlUp).Row
    Range("A1").AutoFilter
    ActiveSheet.Range("$A$1:$A$" & Last).AutoFilter Field:=1, Criteria1:="#"
    Rows("1:" & Last).Offset(1).Select
    Selection.Delete Shift:=xlUp
    Selection.AutoFilter

 

[njimack]

Rather than testing each row, record a macro where you filter column A for "#", and then delete visible cells only.

 

[taigovinda]

I think you'll find AutoFiltering a lot faster. Filter to show the rows with "#" and then delete the rows in one go.

Also, if the order of rows doesn't matter then you can sort by column A first, so that all the # rows are next to each other. Excel can sort, then delete one block a lot faster than it can delete a whole bunch of non-contiguous rows...

 

[justanotheruser]

Hi all! Thank you so much for your extremely fast responses! To reply to you taigovinda, unfortunately the order of the code does matter so I am unable to sort them as you suggested.

Is there a code that will delete the visible cells only, I don't know quite how to do that:

This is my code that creates the auto filter filtering out everything except the #s:

Rows("1:1").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$J$10000").AutoFilter Field:=1, Criteria1:="#"
    ActiveSheet.EntireRow.Delete

Now how do I run down the rows and delete them al (as they are all #s) l?

 

[wigi]

Have a look at SpecialCells(xlCellTypeVisible). That can be used to delete only the visible cells.

 

[marka87uk]

This uses the autofilter to only show the #'s, and then deletes the visible cells.

Selection.AutoFilter Field:=1, Criteria1:="#"
Cells.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Delete Shift:=xlUp

 

[justanotheruser]

Thanks a lot to all of you for your help, wow it's a couple of seconds instead of 10 minutes now