Excel 2007 - macro to delete row based on criteria

[TheRobster]

I have an Excel table with about 20 columns and approx. 400,000 rows of data. Column J contains numerical data and I need to delete some of the rows based on the numbers in this column.

I need a macro that looks at every cell in column J and runs a test something along the lines of "is the value in this cell less than 0 or greater than 10?" and if this returns true I need to delete the entire row.

Can anyone help with how to go about this as I have no idea really!

Thanks
-Rob

[nO_OnE]

Try:

Mistake in code, use the code in the next post

In the For loop; Replace the 4 in "i = 4" to the row you want to start on, and replace J4 in the range with the range you need to start on...

[nO_OnE]

I appoligize, I made a mistake, you must start from the bottom and work your way up, otherwise it will skip rows when it deletes...

This will take you from the bottom to row 4. Again, my code assumes your data starts in row 4, replace the "4"s with the row that your data starts on...

    Dim i As Integer

    For i = Range("J4").End(xlDown).Row to 4 Step -1
        If Cells(i, 10) < 0 Or Cells(i, 10) > 10 Then
            Rows(i).Delete Shift:=xlUp
        End If
    Next i

[TheRobster]

Thanks for the reply. The code seems to work most of the time however I have one additional problem! There are a large number of blank cells in column J that I need the macro to ignore (not delete). Is there any way to change the macro so it ignores blank cells and skips to to next cell?

I already know the range I need to assess which is J2:J405688, if this helps (no need to get the code to determine the start value of i).

I will try and change the code myself but any further help is appreciated.

Thanks
Rob

[nO_OnE]

A blank cell can be "Empty" or "Null", you can check for them in your if statement like:

If isnull(Cells(i, 10)) then
    'this will execute your code if the cell is null

If Not isnull(Cells(i, 10)) then
    'this will execute your code if the cell is not null

You can check for empty cells the same way, just use "isempty" inplace of isnull.

Dim i As Integer
 
    For i = Range("J2").End(xlDown).Row to 2 Step -1
        If Not IsEmpty(Cells(i, 10)) And Not IsNull(Cells(i, 10)) then
            If Cells(i, 10) < 0 Or Cells(i, 10) > 10 Then
                Rows(i).Delete Shift:=xlUp
            End If
        End If
    Next i

[nO_OnE]

I already know the range I need to assess which is J2:J405688

If that's all you want, then you can adjust the For loop like:

    For i = 405688 to 2 Step -1

But if the number of rows changes later on you'll have to manually change your code, the way I originally posted will automatically determine the last row, there really won't be any time benefit to it, if that's what your looking for.

[TheRobster]

That's great, thanks for the help.

[Julian_D]

Hi folks, I'd like to refloat this thread as I'm having a similar inquiry as TheRobster, but my filtering criteria is different and I don't know how to code it:

In column B I have statuses: approved, received, in process, finalized, billed, accounted, etc. I want to delete all rows that are NOT received, in process or finalized. There are currently over 20,000 rows and they add up. Data starts on row 2, after the headers.

How could this be coded? Thank you very much in advance!!

[incidentals]

Dim i As Integer

    For i = Range("b2").End(xlDown).Row to 2 Step -1
        If Not IsEmpty(Cells(i, 2)) And Not IsNull(Cells(i, 2)) then

'use select because it allows simple multiple entries to test against

select case cells(i,2)
case "received","in process","finalized"
   Rows(i).Delete Shift:=xlUp
end select
        End If
    Next i

if the code given by "no one" works then this variation will also work

the variation addresses the b-ness of the problem column 2
here to help (an explaination can be supplied if needed)

[indians034]

I'm having a similar question about a macro I am writing.
I have data from C5 : ???.
I what to look at all of the values from C:?? and if ALL of them are less than 0.05 (rounding to 0.1) then delete the entire row.

Any ideas on code for this would be appreciated. Thank you!

[vbfbryce]

Delete WHICH row? Or do you mean column C?

[indians034]

All rows that that each of the individual cells in that row are less than 0.05.

Ex:
C D E F
5 1.5 0.1 0.0 0.0
6 0.0 0.0 0.0 0.0
7 2.5 2.1 1.7 1.9
8 0.0 0.0 0.0 0.0
9 0.0 0.0 0.0 0.0

I would want it to delete lines 6, 8, and 9

[vbfbryce]

Try this. Not very elegant, and I had data starting in A1, not C5 or wherever yours is:

Sub delRows()
    Dim lastRow As Long
    Dim lastCol As Integer
    Dim i As Long
    Dim allLess As Boolean
    Dim j As Long
    Dim myColumnLetter As String
    
    allLess = True
    
    lastRow = Range("a" & Rows.Count).End(xlUp).Row
    
    For i = lastRow To 1 Step -1
        lastCol = Range("a1").End(xlToRight).Column
        For j = 1 To lastCol
            If j > 26 Then
                myColumnLetter = Chr(Int((j - 1) / 26) + 64) & Chr(((j - 1) Mod 26) + 65)
            Else
                myColumnLetter = Chr(j + 64)
            End If
            If Range(myColumnLetter & i) >= 0.05 Then
                allLess = False
                j = lastCol
            Else
                '
            End If
        Next j
        If allLess = True Then
            Range("a" & i).EntireRow.Delete
        End If
        allLess = True
    Next i
End Sub

[scifi2354]

I also am having trouble writing a similar macro

I have values in Column H that start with 1000000000, of which there are at least 11 rows, i.e. there are at least 11 rows with id = 1000000000. After the 11 rows with id = 1000000000, it continues with at least 11 rows with id = 1000000001, at least 11 rows with id = 1000000002, etc.

I am trying to keep the first 11 rows for each 'id' and want to delete the rest. How do I go about this? I have attached a screenshot, if this is not clear. If you look at the screenshot, there is a 'Time' column, which I think could also be used in helping to write the macro. Although this specific file has 'Time' starting with '1', this is not always the case. Depending on the excel file, it could also start with other numbers. In this specific excel file, I would like to keep 'Time' values between 1-11, but if the first 'id' has its first 'Time' value = 4, then I would like to
keep 'Time' between 4-14.

Thanks for the help.

[scifi2354]

Sorry, I have attached the screenshot here.

I also am having trouble writing a similar macro

I have values in Column H that start with 1000000000, of which there are at least 11 rows, i.e. there are at least 11 rows with id = 1000000000. After the 11 rows with id = 1000000000, it continues with at least 11 rows with id = 1000000001, at least 11 rows with id = 1000000002, etc.

I am trying to keep the first 11 rows for each 'id' and want to delete the rest. How do I go about this? I have attached a screenshot, if this is not clear. If you look at the screenshot, there is a 'Time' column, which I think could also be used in helping to write the macro. Although this specific file has 'Time' starting with '1', this is not always the case. Depending on the excel file, it could also start with other numbers. In this specific excel file, I would like to keep 'Time' values between 1-11, but if the first 'id' has its first 'Time' value = 4, then I would like to
keep 'Time' between 4-14.

Thanks for the help.

[vbfbryce]

I tried to reply earlier, but it didn't make it in...

This should be close, I haven't tested all scenarios:

Sub delRowsGT11()
    Dim i As Long
    Dim lastRow As Long
    Dim currKey As String
    Dim endRow As Long
    Dim startRow As Long
    Dim deleteRow As Long
    
    lastRow = Range("h" & Rows.Count).End(xlUp).Row
    
    currKey = Range("h" & lastRow).Value
    endRow = lastRow
    
    For i = lastRow - 1 To 12 Step -1
        If i = 12 And currKey = Range("h" & endRow).Value Then
            Range("a13", "a" & endRow).EntireRow.Delete
            Exit Sub
        End If
        If Range("h" & i).Value <> currKey Then
            'found first row above with different value
            startRow = i + 1
            If endRow - startRow > 11 Then
                delrow = startRow + 11
                Range("a" & delrow, "a" & endRow).EntireRow.Delete
            End If
            currKey = Range("h" & i).Value
            endRow = i
        End If
    Next i
End Sub

[scifi2354]

Thanks a lot. It's working very well!

[bigdoods]

I have an Excel table with about 3 columns and 9,117 rows of data. Column C contains numerical data and I need to delete some of the rows based on the numbers in this column.

I need a macro that looks at every cell in column C for , any value greater than 17 then the full row needs to be deleted. Can anyone help with how to go about this as I have literally no idea. I desperately need to resolve this. Also I have looked at the replies before this post and I notice it is all code. If you could give me some indication of where the code needs to be entered it would be greatly appreciated.

Thanks in advance
-John

[vbfbryce]

I would start by recording a macro. Depending on your version of Excel, that would be Alt+T+M+R (Tools...Macro...Record Macro). You will provide a name for the macro, not real important exactly what it is called. Then I would repeat the Alt+T+M+R key combination, which ENDS the recording. You have created the "shell" of a macro by doing this. Then, an Alt+T+M+M brings up your list of macros, which should only show the one you just recorded. Highlight the name of it, then click the Edit button, and it will take you to the place where you can put the code like what you see in this thread. Reply back when you get to that point.

Sub delGT17()
    Dim i As Long   'counter variable
    Dim lastRow As Long     'the last row of your data, looking at column C
    
    lastRow = Range("c" & Rows.Count).End(xlUp).Row     'start at the bottom row in Col C, do a "Ctrl+Up"
    
    For i = lastRow To 2 Step -1
        If Range("c" & i).Value > 17 Then   'if the value of cell C, whichever row we're in is > 17
            Range("a" & i).EntireRow.Delete 'delete that entire row
        End If
    Next i
End Sub

[scifi2354]

I tried to reply earlier, but it didn't make it in...

This should be close, I haven't tested all scenarios:

Sub delRowsGT11()
    Dim i As Long
    Dim lastRow As Long
    Dim currKey As String
    Dim endRow As Long
    Dim startRow As Long
    Dim deleteRow As Long
    
    lastRow = Range("h" & Rows.Count).End(xlUp).Row
    
    currKey = Range("h" & lastRow).Value
    endRow = lastRow
    
    For i = lastRow - 1 To 12 Step -1
        If i = 12 And currKey = Range("h" & endRow).Value Then
            Range("a13", "a" & endRow).EntireRow.Delete
            Exit Sub
        End If
        If Range("h" & i).Value <> currKey Then
            'found first row above with different value
            startRow = i + 1
            If endRow - startRow > 11 Then
                delrow = startRow + 11
                Range("a" & delrow, "a" & endRow).EntireRow.Delete
            End If
            currKey = Range("h" & i).Value
            endRow = i
        End If
    Next i
End Sub

Thanks again for the macro. Is there any way to upload to an excel file? There's a couple of files for which the macro does not work completely, i.e. some rows are not being clipped.

[vbfbryce]

Here is what I was using:

[scifi2354]

I have attached two files. The first file 'scifi2354.xls' is the original excel file that I want to run the macro on. The second file 'scifi2354 - v2' is the file after the macro has been run. The problem is that some are not clipped to the first 11 rows, e.g. 1000000031, 1000000034, and 1000000040.

Is there a way to alter the macro so that it sucessfully clips all groups to 11 rows?

Thanks.

Here is what I was using:

[vbfbryce]

I updated the code a bit and it seems to work on your "before" file:

Sub delRowsGT11()
    Dim i As Long
    Dim lastRow As Long
    Dim currKey As String
    Dim endRow As Long
    Dim startRow As Long
    Dim deleteRow As Long
    
    Application.ScreenUpdating = False
    
    lastRow = Range("h" & Rows.Count).End(xlUp).Row
    currKey = Range("h" & lastRow).Value
    endRow = lastRow
    
    For i = lastRow - 1 To 12 Step -1
        If i = 12 And currKey = Range("h" & endRow).Value Then
            Range("a13", "a" & endRow).EntireRow.Delete
            Exit Sub
        End If
        If Range("h" & i).Value <> currKey Then
            'found first row above with different value
            startRow = i + 1
            If endRow - startRow > 10 Then
                delrow = startRow + 11
                Range("a" & delrow, "a" & endRow).EntireRow.Delete
                endRow = i
            Else
                'endRow = i - 1
                endRow = startRow - 1
            End If
            currKey = Range("h" & i).Value
        End If
    Next i
End Sub