More efficient and faster way to delete Rows?

Hi All,
I have this script that deletes the rows of records if Column A does not have any value. (Not checked).
There are columns A - H. Column A is used for me to indicate if I want to keep the row by inputting any value.

While it works, the issue is that it takes a long time to process and 20000 records can easily take more than 10 mins.
Is there more efficient way that anyone knows of?

Sub Delete()
Dim MyRange As Range
Dim X As Long
Dim myLastRow As Long

Application.ScreenUpdating = False
Set MyRange = Intersect(ActiveSheet.UsedRange, Range("A:A"))
myLastRow = MyRange.Row + MyRange.Rows.Count - 1
For X = myLastRow To 7 Step -1
If Cells(X, 1).Value = False Or Cells(X, 1).Value = "" Then
Cells(X, 1).EntireRow.Delete
End If
Next X

Application.ScreenUpdating = True

End Sub

Regards,
Nironto

Originally Posted by nironto

Hi All,
I have this script that deletes the rows of records if Column A does not have any value. (Not checked).
There are columns A - H. Column A is used for me to indicate if I want to keep the row by inputting any value.

While it works, the issue is that it takes a long time to process and 20000 records can easily take more than 10 mins.
Is there more efficient way that anyone knows of?
...
...
Regards,
Nironto

try this

Sub delete_unwanted_rows()
Dim lr&, lc&, q&
lr = Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
lc = Cells.Find("*", searchorder:=xlByColumns, searchdirection:=xlPrevious).Column + 1

With Cells(1).Resize(lr)
    .Offset(, lc - 1) = Evaluate("if(" & .Address & "="""","""",1)")
    .Resize(, lc).Sort Cells(lc), Header:=xlNo
    q = Application.Count(.Offset(, lc - 1))
    If q < lr Then Range(Cells(q + 1, 1), Cells(lr, lc - 1)).ClearContents
    .Offset(, lc - 1).ClearContents
End With

End Sub

Thanks Marcol,
But if i change according to your method, it won't delete the rows.
Basically it has to know the lastrow of records and delete only those rows that are not marked as "keep".
I had attached the excel. In excel there are only 10000+ rows, but you can drag down to 50k plus rows to understand the pain my users undergo while waiting for it to end.

Regards,
Nironto

Hi Kalak,
Thanks! I like your script. It is super efficient!
But question is it will delete also the header. (The Row A1.) How do I only delete from row 2 onwards?

Regards.
Nironto

Sub test()
    With Range("a2").CurrentRegion
        .AutoFilter 1, ""
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
End Sub

Thanks Jindon! Tested out your script. It is simple and works beautifully!

Regards,
Nironto

change is very minor (in red below). you didn't say if you had a header so I had to guess. but guessed wrong

Sub delete_unwanted_rows()
Dim lr&, lc&, q&
lr = Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
lc = Cells.Find("*", searchorder:=xlByColumns, searchdirection:=xlPrevious).Column + 1

With Cells(1).Resize(lr)
    .Offset(, lc - 1) = Evaluate("if(" & .Address & "="""","""",1)")
    .Resize(, lc).Sort Cells(lc), Header:=xlYes
    q = Application.Count(.Offset(, lc - 1))
    If q < lr Then Range(Cells(q + 1, 1), Cells(lr, lc - 1)).ClearContents
    .Offset(, lc - 1).ClearContents
End With

End Sub

You are welcome.

This is a great site and learning experience because I get to learn so many other ways of doing the same thing.

Hi Kalak,
I found a issue with your script. If i select row 5-10, then is no issue.
But if in reality, the users don't select in running sequence. If i select 5-10, then 20-25. ( Skipping some records in between)
Then the wrong records gets deleted.

Regards,
Nironto

Originally Posted by nironto

This is a great site and learning experience because I get to learn so many other ways of doing the same thing.

Hi Kalak,
I found a issue with your script. If i select row 5-10, then is no issue.
But if in reality, the users don't select in running sequence. If i select 5-10, then 20-25. ( Skipping some records in between)
Then the wrong records gets deleted.

Regards,
Nironto

Nironto,

I'm afraid that I don't follow you.

If by "select" you mean the Range(A5:A10) AND the Range(A20:25) are both empty, then that code will delete only the rows containing those empty cells and no other rows. Were you asking for something else?
It will actually delete all rows with randomly scattered blank cells in Col A, contiguous or not, up to a million or more rows, quickly and correctly.

If you mean something else by "select" then ...??? You didn't seem to say anything about "select" in your opening post.

You marked the thread as solved so you're happy with it's outcome. So that's a good outcome for all involved.

Hi Kalak,
The issue is solved but I am using your method in another spreadsheet but the lastrow of records always gets deleted.(since there is also one method provided by Jindon, thus the issue is marked as solved).

But your method is interesting and I want to learn more from you.

Refer to attachment. e.g I selected 12 records, but i end up with 11.


Thanks again for you help! Appreciated.

Regards,
Nironto

Solved it! If I changed to 2. It got it all correct!

If q < lr Then Range(Cells(q + 2, 1), Cells(lr, lc - 1)).ClearContents

Cheers to all!

Regards.
Nironto

Thus very solved indeed.

Anyway. Good luck!