PDA

View Full Version : Solved: Delete row range based on criteria in multiple columns



SteveG
08-01-2007, 09:41 AM
Hi All,

I am trying to delete a row from column A:M if certain criteria is met in column C and F. So,

IF C="Test" & F="Delete" OR F = "Delete2" then delete A:M of the corresponding row #.

I am currently using the code below which enables me to delete the entire row but I only want to delete A:M.

Sub DeleteERow()
Application.ScreenUpdating = False
Dim x As Long
For x = Range("C65536").End(xlUp).row To 2 Step -1
If Cells(x, 3).Value <> "XOM" Then Cells(x, 3).EntireRow.Delete
Next x
For x = Range("F65536").End(xlUp).row To 2 Step -1
If Cells(x, 6).Value = "RejectGeneral" Or _
Cells(x, 6).Value = "CorpActionReject" Then Cells(x, 3).EntireRow.Delete
Next x
End Sub

Hope this makes sense.

Thanks in advance,

Steve

Bob Phillips
08-01-2007, 10:48 AM
You can't delete the cells, you can clear them



Sub DeleteERow()
Dim mpCLastRow
Dim mpFLastRow
Dim mplastrow
Dim x As Long

Application.ScreenUpdating = False

mpCLastRow = Range("C65536").End(xlUp).Row
mpFLastRow = Range("F65536").End(xlUp).Row
mplastrow = Application.Max(mpCLastRow, mpFLastRow)
For x = mplastrow To 2 Step -1
If Cells(x, 3).Value <> "XOM" Or _
Cells(x, 6).Value = "RejectGeneral" Or _
Cells(x, 6).Value = "CorpActionReject" Then _
Cells(x, "A").Resize(, 13).ClearContents
Next x
End Sub

SteveG
08-01-2007, 11:17 AM
xld,

Thanks for your solution. Just out of curiosity I changed the code to see what would happen if I changed the ClearContents to Delete.

Sub DeleteERowTest()
Dim mpCLastRow
Dim mpFLastRow
Dim mplastrow
Dim x As Long
Application.ScreenUpdating = False

mpCLastRow = Range("C65536").End(xlUp).Row
mpFLastRow = Range("F65536").End(xlUp).Row
mplastrow = Application.Max(mpCLastRow, mpFLastRow)
For x = mplastrow To 2 Step -1
If Cells(x, 3).Value <> "XOM" Or _
Cells(x, 6).Value = "RejectGeneral" Or _
Cells(x, 6).Value = "CorpActionReject" Then _
Cells(x, "A").Resize(, 13).Delete
Next x
End Sub

It seemed to work without an issue. I typed in data in column N and it was still there after I ran the code and the rows shifted up. Is there a reason I should not do this? I only ask because you indicated I could not delete the rows only clear the contents. Not sure if there are any negative impact this could have.


Thanks again for your help!

Steve

Bob Phillips
08-01-2007, 11:30 AM
It can be done, but it gets the data out of alignment. For instance, if you delete A13:M13, what was in A14:M14 will now become part of row 13 and so on down, whereas the rest of the row remaions as was.

SteveG
08-01-2007, 12:33 PM
It appears that all of row A14:M14 just moves up and is now in A13:M13 which is ok for my situation. That's actually what I want to happen. I'm not sure what you mean when you say the rest of the row remains as was. Will only certain cells from A13:M13 be deleted and the info from A14:M14 shift up only where the previous cells in A13:M13 were deleted?

As a test, I ran the macro I was using to delete the entire row and then the macro you provided using Delete rather than ClearContents. Both matched up perfectly. Just to be sure I went in and used the auto filter to get the data I was looking for and compared it to both the data after running my original macro and then yours. It matched up okay again. I trust you when you say there is some potential issue but I am not sure it impacts my desired results. Could you elaborate?

Thanks again for your assistance!
Steve

Bob Phillips
08-01-2007, 01:37 PM
Take a look at these two pictures. The first shows the data before deleting.

The second deletes just twocolumns in the row, not the whole row, C & D in row 9. Before the delete, Kentucky had a population of 871,441. After the delete, it has a population of Massachussetts population, 1,999,144. Because it only deletes cells, the following rows get shunted up out of alignment.

SteveG
08-01-2007, 01:53 PM
Not seeing the pictures attached. I understand what you're saying though. I guess I could add a sort after clearing the data to shift the data up. Funny that it would clear the data in all cells in the row but not delete. Is there a reason for this?

Thanks!

Steve

Bob Phillips
08-01-2007, 01:58 PM
The forum has ridiculously tight rules on GIFs, can't get it small nough.

Why sort, why not just ClearContents?

Don't understand that last part.

SteveG
08-02-2007, 07:11 AM
The data is part of a report that the recipients need to review and return to me for finalization. There are formulas in columns N:P that calculate each line item's status in A7:M50000 and then in Q:S there are drop downs for the reviewers to select from that impact the final calculations. The initial file that comes in is 30,000+ lines (you helped me with the import code in a prior post) but after scrubbing the data using this macro it only winds up being around 2,500 of which the reviewers only need to look at items that show a FALSE return from the formula in column P. I just use the auto filter to hide the items they don't need to look at. I suppose I could use that rather than a sort in the macro but I was just trying to take out as much human intervention as possible. Ultimately I'd like to write an import routine that only pulled in data from the .csv file that only met certain criteria. i.e. column C = "XOM" and F <> RejectGeneral or CorpActionReject. I'm just not that versed in VBA yet!

So to make a long story short, it's easier for them to read.

Thanks again for all your help!

Steve