86 Seconds to run my bit of Code to delete blank rows ! Would like it to run faster, learning

[DarrenBurke]

Evening all
Need to remove blank rows from worksheet. They cause an untidy database. 86 seconds to clear the blanks..... it works but i am sure the hive minds can make it run faster

My Xl2BB

Michael Pickup.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	A/ CODENUMNUM	DATE CREATED	REF	NUM	PICK-UP DATE	COMPANY	BUILDING	STREET ADDRESS	SUBURB	TOWN	CROSS ROAD	CONTACT 1	CONTACT 2	TEL NUMBER	FAX NUMBER	REPRESENTATIVE	DONAT	QTY	DELIVER PRODUCT	A/ CODE	NUM	NUM	F/CODE	NUM	NUM
2	0125482354	2022/07/07	MF	355	2022/07/08	ZAMBESI GLASS&ALUMINIUM	MONTANA VALUE CENTRE	151 CALLIANDRA STR	MONTANA	PRETORIA	TIBOUCHINA	CORIN	RECEPTION	012		MICHAEL FERREIRA	Glass	1	TAX INVOICE	012	548	2354
3	0119082654	2022/07/06	MF	354	2022/07/07	JMR TRANSPORT SOLUTIONS(PTY)LTD		4 BORAX STREET	ALRODE	ALBERTON		MEV HEYSTECK	ASK FOR HER CASH	011	CASH	MICHAEL FERREIRA	550,00	1	TAX INVOICE	011	908	2654	CASH
4	0117933695	2022/07/06	MF	351	2022/07/07	GRAYTEX METALS		375 KRUGER STREET	STRIJOMPARK	RANDBURG		BRIN	AS PER JASON	011	CASH	MICHAEL FERREIRA	200,00	1	TAX INVOICE	011	793	3695	CASH
5	0829725647	2022/07/06	MF	352	2022/07/07	CAR CLINIC		49 SHAFT ROAD	KNIGHTS	GERMISTON		JANNIE	INA	082	CASH	MICHAEL FERREIRA	100,00	1	TAX INVOICE	082	972	5647	CASH
6	0761393126	2012/10/18	MF	1015	2012/10/22	THE BRAZEN HEAD	RIVER CRESENT CENTRE		C/O BETHAL	WITBANK	MANDELA	NICK CHRISOCHOFF	BERNI	076		MICHAEL FERREIRA	150,00	1	TICKETS	076	139	3126
7	0132826078	2012/10/18	MF	1014	2012/10/22	NU MID GLASS	AGTER POSKANTOOR	26 SADC STR		MIDDELBURG		PAT	DASSIE	013		MICHAEL FERREIRA	200,00	1	TAX INVOICE	013	282	6078
8	0823396328	2012/10/17	MF	1013	2012/10/18	ANTON LOMBAARD INC.	OAK AVENUE OFFICE PARK	372 OAK AVENUE	BTF PAYMENT	FERNDALE		ANTON LOMBAARD	LIZ	082		MICHAEL FERREIRA	400,00	1	SHERRY HAMPER	082	339	6328
9	0137527653	2012/07/04	MF	862	2012/07/05	LOWVELD COMPRESSOR SERVICES	ATLAS COPCO	14 OLD PRETORIA ROAD	VINTONIA	NELSPRUIT		FRANCOIS	MARALIZE	013		MICHAEL FERREIRA	600,00	1	4 X BOOKS TICKETS	013	752	7653
10	0119185961	2008/01/01	MF	209	2019/04/03	LARMESH EXPANDED METALS	0	7 TOP RD	ANDERBOLT	BOKSBURG		ROBERT	KIRST	011		MICHAEL FERREIRA	500,00	1	TAX INVOICE	011	918	5961
11	0118944714	2008/01/01	MF	210	2008/01/03	CRYSTAL SPIRITS	LAKEFIELD SQR	LAKEFIELD AVE	LAKEFIELD	BENONI	0	CHANTEL	0	011		MICHAEL FERREIRA	100,00	1	TAX INVOICE	011	894	4714
12	0823396328	2012/10/17	MF	1013	2012/10/18	ANTON LOMBAARD INC.	OAK AVENUE OFFICE PARK	372 OAK AVENUE	BTF PAYMENT	FERNDALE		ANTON LOMBAARD	LIZ	082		MICHAEL FERREIRA	400,00	1	SHERRY HAMPER	082	339	6328
13	0137527653	2012/07/04	MF	862	2012/07/05	LOWVELD COMPRESSOR SERVICES	ATLAS COPCO	14 OLD PRETORIA ROAD	VINTONIA	NELSPRUIT		FRANCOIS	MARALIZE	013		MICHAEL FERREIRA	600,00	1	4 X BOOKS TICKETS	013	752	7653
14	0119185961	2008/01/01	MF	209	2019/04/03	LARMESH EXPANDED METALS	0	7 TOP RD	ANDERBOLT	BOKSBURG		ROBERT	KIRST	011		MICHAEL FERREIRA	500,00	1	TAX INVOICE	011	918	5961
15	0125482354	2022/07/07	MF	355	2022/07/08	ZAMBESI GLASS&ALUMINIUM	MONTANA VALUE CENTRE	151 CALLIANDRA STR	MONTANA	PRETORIA	TIBOUCHINA	CORIN	RECEPTION	012		MICHAEL FERREIRA	Glass	1	TAX INVOICE	012	548	2354
16	0119082654	2022/07/06	MF	354	2022/07/07	JMR TRANSPORT SOLUTIONS(PTY)LTD		4 BORAX STREET	ALRODE	ALBERTON		MEV HEYSTECK	ASK FOR HER CASH	011	CASH	MICHAEL FERREIRA	550,00	1	TAX INVOICE	011	908	2654	CASH
17	0125482354	2022/07/16	MF	777		ZAMBESI GLASS&ALUMINIUM	MONTANA VALUE CENTRE	151 CALLIANDRA STR	MONTANA	PRETORIA	TIBOUCHINA	CORIN	RECEPTION	012		MICHAEL FERREIRA		1	TAX INVOICE	012	548	2354
18	0118944714	2022/07/16	MF	999		CRYSTAL SPIRITS	LAKEFIELD SQR	LAKEFIELD AVE	LAKEFIELD	BENONI		CHANTEL		011		MICHAEL FERREIRA		1	TAX INVOICE	011	894	4714
19	0119082654	2022/07/16	MF	1		JMR TRANSPORT SOLUTIONS(PTY)LTD		4 BORAX STREET	ALRODE	ALBERTON		MEV HEYSTECK	ASK FOR HER CASH	011		MICHAEL FERREIRA		1	TAX INVOICE	011	908	2654
20	0823396328	2022/07/16	MF	1		ANTON LOMBAARD INC.	OAK AVENUE OFFICE PARK	372 OAK AVENUE	BTF PAYMENT	FERNDALE		ANTON LOMBAARD	LIZ	082		MICHAEL FERREIRA		1	TAX INVOICE	082	339	6328
21		2022/07/16		1														1	TAX INVOICE
22		2022/07/16		1														1	TAX INVOICE
23
24
25
26
27
28
29
30
31
32
33
34
35
36
DATABASE BUILDING

Cell Formulas
Range	Formula
A1:A36	A1	=CONCATENATE(T1,U1,V1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C:C	Cell	contains a blank value	text	NO
Q2:Q11	Cell Value	="0.00"	text	NO
C:C	Cell	contains a blank value	text	NO

My Current VBA it works but 86 seconds for such a small amout of data to check, at a loss

Option Explicit

Sub sbDelete_Rows_IF_Cell_Is_Blank1()

Application.ScreenUpdating = False

    Dim lRow As Long
    Dim iCntr As Long
        lRow = 1500
    For iCntr = lRow To 1 Step -1
    If Trim(Cells(iCntr, 1)) = "" Then
        Rows(iCntr).Delete
    End If
    Next

    Range("A2").Select
    Selection.AutoFill Destination:=Range("A2:A1500"), Type:=xlFillDefault
    Range("A2:A1500").Select
        Range("B1").Select
    Application.ScreenUpdating = True

End Sub

Thank you for your time

 

[Peter_SSs]

Any reason that you cannot continue to use the code that "works in milliseconds" per your comment here?

 

[Ottsel]

Give this a try.

Option Explicit
Sub sbDelete_Rows_IF_Cell_Is_Blank1()
    Dim i As Long
    Dim lastrow As Long
    lastrow = Cells(rows.Count, "A").End(xlUp).Row
    Application.ScreenUpdating = False
   
    For i = lastrow To 2 Step -1
        If Range("A" & i).Value = "" Then Range("A" & i).EntireRow.Delete
    Next i

    Range("A2").Select
    Selection.AutoFill Destination:=Range("A2:A1500"), Type:=xlFillDefault
    Range("A2:A1500").Select
    Range("B1").Select
   
    Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

BTW, isn't the bottom part of that code in post #1 just going to re-fill all those rows with formulas returning exactly what is in them now?

Are all the blanks together at the bottom of the other non-blank values in column A like your sample?

 

[Ottsel]

BTW, isn't the bottom part of that code in post #1 just going to re-fill all those rows with formulas returning exactly what is in them now?

Are all the blanks together at the bottom of the other non-blank values in column A like your sample?

I thought the same, but figured this was just an example and maybe its required the real document?