VBA to delete row based on cell value.

[semidevilz]

I found a vba script that works in deleting row based on cell value, but I would like help with
1. understanding it
2. make it run faster

I have a spreadsheet that has Dates in Column C. Every week, before I run my report, I delete all my data from the most recent 90 days. Cell "J2" is the "cut off date" that is automatically calculated. So if J2 = 01/01/2016, the code will delete all rows with dates > 01/01/2016

Sub DeleteRow()
Application.ScreenUpdating = False
Dim LR As Long
For LR = Range("C" & Rows.Count).End(xlUp).Row To 2 Step -1
If Range("C" & LR).Value > Range("J2").Value Then
Rows(LR).EntireRow.Delete
End If
Next LR

Question 1:
I still dont get what Range("C" & Row.count) means. Is it just counting the rows from column C?
What is Row To 2 Step -1?

Question 2:
The code seems to work fine, but my worksheet has almost 1 million rows and when I run it, it just keeps clocking. is there a way to run faster? I haven't had a successful execution yet.

will appreicate any feedback on the question or changes to the code.

 

[Peter_SSs]

trying to get something similar.

In my case, I want to:
-check if cell under column F contains a number;
-if not a number, delete the entire row.


How can I do this ?

1. Is your data sorted by column F in any way? If so, please give some details about the sort.
2. How many rows of data do you have (approximately)?

 

[excel01noob]

HI Michael M, unfortunately does not seem to work

Hi Peter_SSs
1. no sort of any kind, the content of that cell should be always an unique number;
2. the rows of data vary each day
3. forgot to mention, I have headers so the procedure should only look as from row 2.

 

[Peter_SSs]

2. the rows of data vary each day

But is it a few hundred rows, a few thousand rows, or about 1,000,000 rows etc?

 

[excel01noob]

But is it a few hundred rows, a few thousand rows, or about 1,000,000 rows etc?

best to use up to a thousand rows

 

[Peter_SSs]

best to use up to a thousand rows

OK, that is not a lot of rows so speed (which is why I asked the question) may not be an issue. However, this should be very fast.

Sub Del_Non_Numbers()
  Dim a As Variant, b As Variant
  Dim nc As Long, i As Long, k As Long
 
  nc = Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
  a = Range("F2", Range("F" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    If Not IsNumeric(a(i, 1)) Or IsEmpty(a(i, 1)) Then
      b(i, 1) = 1
      k = k + 1
    End If
  Next i
  If k > 0 Then
    Application.ScreenUpdating = False
    With Range("A2").Resize(UBound(a), nc)
      .Columns(nc).Value = b
      .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo
      .Resize(k).EntireRow.Delete
    End With
    Application.ScreenUpdating = True
  End If
End Sub

A few comments though:
1. This code (& Michael's) will delete a row if the cell contains a date, even though a date in Excel is stored as a number.
2. IsNumeric has some other 'quirks' that could result in rows being deleted when they shouldn't but those circumstances may well not arise for you.
3. This code will delete rows where the cell is empty (since it does not contain a number) whereas Michael's code does not delete such rows.

Post back if you find any issues with your data in relation to these points - or anything else that arises.

@ Michael
You also have some unnecessary calculation happening in your For i = ... line. It could be done using column F only rather than columns F and A.

<del>For i = .Cells(.Cells(.Rows.Count, 6).End(xlUp).Row, 1).Row To 2 Step -1</del>
For i = .Cells(.Rows.Count, 6).End(xlUp).Row To 2 Step -1

 

[Michael M]

OOps...
Missed that, thanks Peter !!

 

[excel01noob]

Hi Peter

Getting almost there.

After the data, my report has:

-a complete blank row;
-next row shows the total of rows with data (only cell A contains information);
-next row is also blank;
-next row shows the timestamp (cell A) when the report was generated.

I understood that as these rows contain data but cell F in the row is blank, the entire row would be deleted (what I want in fact) but they were not deleted with the code.
did I miss something ?

 

[Peter_SSs]

Try making this change in the code.

<del>a = Range("F2", Range("F" & Rows.Count).End(xlUp)).Value</del>
a = Range("F2:F" & Range("A" & Rows.Count).End(xlUp).Row).Value

 

[excel01noob]

Try making this change in the code.

<del>a = Range("F2", Range("F" & Rows.Count).End(xlUp)).Value</del>
a = Range("F2:F" & Range("A" & Rows.Count).End(xlUp).Row).Value

Perfect, thank you very much!!!

 

[Peter_SSs]

Perfect, thank you very much!!!

You're welcome. Thanks for the confirmation.