Deleting empty rows fastest way possible

[adnan1975]

I am sure this has been discussed many times before but here it is again. I am trying to delete empty rows from a spreadsheet with more than 10000 rows. I am using the following code but it doesn't work.

Sub RowDeleter()
    Dim sht As Worksheet
    Dim r As Long
    Dim EndRow As Long
    Dim TCount As Long
    Dim s As Date
    Dim e As Date

    Application.ScreenUpdating = True
    r = 2       'Initialise row number
    s = Now     'Start Time
    Set sht = ActiveSheet
    EndRow = sht.Cells.SpecialCells(xlCellTypeLastCell).Row

    'Check if "Test String" is found in Column 1
    TCount = Application.WorksheetFunction.CountIf(sht.Columns(1), "")
    If TCount > 0 Then

        'loop through to the End row
        While r <= EndRow
            If InStr(sht.Cells(r, 1).Text, "") > 0 Then
                sht.Rows(r).Delete Shift:=xlUp
                r = r - 1
            End If
            r = r + 1
        Wend
    End If
    e = Now  'End Time
    D = (Hour(e) * 360 + Minute(e) * 60 + Second(e)) - (Hour(s) * 360 + Minute(s) * 60 + Second(s))
    Application.ScreenUpdating = True
    DurationTime = TimeSerial(0, 0, D)
    MsgBox Format(DurationTime, "hh:mm:ss")
End Sub

Segment	Country	Product	Discount Band	Units Sold	Manufacturing Price	Sale Price	Gross Sales	Discounts
Government	Canada	Carretera	None	1618.5​	$ 3.00	$ 20.00	$ 32,370.00	$ -
Government	Germany	Carretera	None	1321​	$ 3.00	$ 20.00	$ 26,420.00	$ -
Midmarket	France	Carretera	None	2178​	$ 3.00	$ 15.00	$ 32,670.00	$ -
Midmarket	Germany	Carretera	None	888​	$ 3.00	$ 15.00	$ 13,320.00	$ -
Midmarket	Mexico	Carretera	None	2470​	$ 3.00	$ 15.00	$ 37,050.00	$ -
Government	Germany	Carretera	None	1513​	$ 3.00	$ 350.00	$ 529,550.00	$ -
Midmarket	Germany	Montana	None	921​	$ 5.00	$ 15.00	$ 13,815.00	$ -
Channel Partners	Canada	Montana	None	2518​	$ 5.00	$ 12.00	$ 30,216.00	$ -
Government	France	Montana	None	1899​	$ 5.00	$ 20.00	$ 37,980.00	$ -
Channel Partners	Germany	Montana	None	1545​	$ 5.00	$ 12.00	$ 18,540.00	$ -
Midmarket	Mexico	Montana	None	2470​	$ 5.00	$ 15.00	$ 37,050.00	$ -
Enterprise	Canada	Montana	None	2665.5​	$ 5.00	$ 125.00	$ 333,187.50	$ -
Small Business	Mexico	Montana	None	958​	$ 5.00	$ 300.00	$ 287,400.00	$ -
Government	Germany	Montana	None	2146​	$ 5.00	$ 7.00	$ 15,022.00	$ -
Enterprise	Canada	Montana	None	345​	$ 5.00	$ 125.00	$ 43,125.00	$ -
Midmarket	United States of America	Montana	None	615​	$ 5.00	$ 15.00	$ 9,225.00	$ -
Government	Canada	Paseo	None	292​	$ 10.00	$ 20.00	$ 5,840.00	$ -
Midmarket	Mexico	Paseo	None	974​	$ 10.00	$ 15.00	$ 14,610.00	$ -
Channel Partners	Canada	Paseo	None	2518​	$ 10.00	$ 12.00	$ 30,216.00	$ -
Government	Germany	Paseo	None	1006​	$ 10.00	$ 350.00	$ 352,100.00	$ -
Channel Partners	Germany	Paseo	None	367​	$ 10.00	$ 12.00	$ 4,404.00	$ -
Government	Mexico	Paseo	None	883​	$ 10.00	$ 7.00	$ 6,181.00	$ -

Any help is highly appreciated.
I tried to use L2BB add in but it is blocked by excel and dont know how to unlock it.

 

[Peter_SSs]

I will tomorrow Peter because it is way past my beauty sleep time (midnight)

 

[jolivanes]

Hi Peter.
Sorry for being a little late.

Device name: Laptop
Processor: Intel(R) Core(TM) i7-4600U CPU @ 2.10GHz 2.70 GHz
Installed RAM: 16.0 GB
System type: 64-bit operating system, x64-based processor

My findings:
10,825 Rows total of which 902 Rows are empty.
With Union: 1.40 seconds
With Arrays Different Sheet: 0.14 seconds
With Arrays Same Sheet: 0.23 seconds
Michael M's code: 0.77 seconds
Peter SSs' code: 0.09 seconds

 

[jolivanes]

BTW, if you use ScreenUpdating False/True, 3 macros change very slightly.
Union, no change
Arrays different sheet, no change
Arrays same sheet, 0.20 seconds
Michael M's code, 0.74 seconds
Peter SSs' code, 0.08 seconds

 

[Peter_SSs]

Thanks for the updated information.

BTW, if you use ScreenUpdating False/True, 3 macros change very slightly.

You have mine as one that changed. My code already has screen updating off/on covering the part of the code where anything on the sheet changes so any change in timing is coincidental, not related to screen updating.

 

[jolivanes]

Re: Code already has ScreenUpdating False/True on your (Peter's) code.
Yes, changes are probably because every time you run it, it changes slightly.