Tuesday, July 24, 2018

excel - Delete Row Based on Cell Contents

I'm trying to check the contents of the cells in column Q and delete the rows that have a 0 in that column.



The macro should start checking in column Q at cell Q11 and stop when it encounters the cell containing the text "END". When finished it should select the cell at the upper left corner of the spreadsheet, which would normally be A1, but I have a merged cell there, so it's A1:K2.



Here are my two most recent versions of the macro:




'My second to last attempt

Sub DeleteRowMacro1()
Dim i As Integer
i = 11

Do
Cells(i, 17).Activate


If ActiveCell.Value = 0 Then
ActiveCell.EntireRow.Delete
End If

i = i + 1
Loop Until ActiveCell.Value = "END"
Range("A1:K2").Select
End Sub





'My last attempt

Sub DeleteRowMacro2()
Dim i As Integer
i = 11
GoTo Filter
Filter:
Cells(i, 17).Activate

If ActiveCell.Value = "END" Then GoTo EndingCondition
If ActiveCell.Value = "" Then GoTo KeepCondition
If ActiveCell.Value = 0 Then GoTo DeleteCondition
If ActiveCell.Value > 0 Then GoTo KeepCondition

EndingCondition:
Range("A1:K2").Select
KeepCondition:
i = i + 1
GoTo Filter

DeleteCondition:
ActiveCell.EntireRow.Delete
i = i + 1
GoTo Filter

End Sub


What DeleteRowMacro1() Does:




It leaves the row if there is text or a number greater than 0 in column Q, but it deletes the rows with cells with a 0 AND blank cells. I want to keep the rows with the blank cells.



This macro seems to be incapable of checking the 450 or so cells between the Q11 and the cell with "END" in one run. It only deletes about half of the rows it should each time. The first 10 or so rows are always done correctly, but then it appears to randomly choose rows with a zero or a blank in column Q to delete.



If I run the macro 7 or 8 times, it will eventually delete all of the rows with a 0 and the ones that are blank too. I would like it to completely do it's job in one run and not delete the rows with blank cells.



What DeleteRowMacro2() Does:



It never stops at "END".




I have to run it 7 or 8 times to completely get rid of all of the rows with a 0 in column Q. It also appears to randomly check cells for deletion (and once again besides the first 10 or so).



Because it never ends when I run it, the area of my screen where the spreadsheet is turns black and all I can see there is the green selected cell box flickering up and down at random locations in the Q column until it gets to a row number in the 32,000s. After that my screen returns to show the normal white spreadsheet and a box appears that says Run-time error '6': Overflow.



Please note: After I click "End" on the error box I can see that the macro worked as described above.

No comments:

Post a Comment

plot explanation - Why did Peaches' mom hang on the tree? - Movies & TV

In the middle of the movie Ice Age: Continental Drift Peaches' mom asked Peaches to go to sleep. Then, she hung on the tree. This parti...