Printable View
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
Try:
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...vb Code:
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...
Code: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
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
A blank cell can be "Empty" or "Null", you can check for them in your if statement like:
You can check for empty cells the same way, just use "isempty" inplace of isnull.Code: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
vb Code:
If that's all you want, then you can adjust the For loop like:Quote:
I already know the range I need to assess which is J2:J405688
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.Code:For i = 405688 to 2 Step -1
That's great, thanks for the help. :)
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!!
if the code given by "no one" works then this variation will also workCode: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
the variation addresses the b-ness of the problem column 2
here to help (an explaination can be supplied if needed)
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!
Delete WHICH row? Or do you mean column C?
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
Try this. Not very elegant, and I had data starting in A1, not C5 or wherever yours is:
Code: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
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.
Sorry, I have attached the screenshot here.
Quote:
Originally Posted by scifi2354
I tried to reply earlier, but it didn't make it in...
This should be close, I haven't tested all scenarios:
Code: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 a lot. It's working very well! :)
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
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.
Code: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
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.Quote:
Originally Posted by vbfbryce
Here is what I was using:
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.
Quote:
Originally Posted by vbfbryce
I updated the code a bit and it seems to work on your "before" file:
Code: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