Solved: Delete Row Based On Value

[gimli]

Hi all,

Im having a problem with some code to delete rows...I actually asked a similar question in another thread and marked it solved. So I started this thread because its a bit different..hopefully thats ok.

I have the following code to remove rows if column 10 <> 1. I have code in the cells that returns a value of 1 or "". So im trying to set up the code remove rows if the value <>1 but I think the formula is causing a mismatch error...any way around that?
thanks for the help


Option Explicit
Sub DeleteRows()
Dim n As Long
Dim nlast As Long
Dim rw As Range
Set rw = ActiveWorkbook.ActiveSheet.UsedRange.Rows
nlast = rw.Count
For n = nlast To 1 Step -1
If (rw.Cells(n, 10).Value <> 1) Then
rw.Rows(n).Delete
End If
Next n

End Sub

[nepotist]

Sub DeleteRows()
Dim n As Long
Dim nlast As Long
Dim rw As Range
Set rw = ActiveWorkbook.ActiveSheet.UsedRange.Rows
nlast = rw.Count
For n = nlast To 1 Step -1
If (Cells(n, 10).Value <> 1) Then
Cells(n, 10).EntireRow.Delete
End If
Next n

End Sub

[gimli]

Ah yes

thank you works great

:yes

[mdmackillop]

Hi Nepotist
A word of caution. UsedRange.Rows does not count all rows as the range may not start at row 1. Open a new sheet, Enter data in Rows 2 & 4 and try
Set rw = ActiveWorkbook.ActiveSheet.UsedRange.Rows
rw.Select

[gimli]

Hrmm still getting an error

I attached a file if you dont mind taking a quick look.

If the value in column J is not equal to 1 I want the row to be deleted. I really only need to effect rows 1 thru 50 at the most.

thanks in advance.

[nepotist]

Thanks for the Info MD, I have never used Usedrange before and to be honest I dint even knew that it exists. I thought it was a user defined range name.
I understand it now. Thank you !!

Gimli, I dont see any error, It works fine at my end, where exactly are you getting the error.

[gimli]

Grrr I hate when that happens...I ran the macro a few times and it seemed ok. I would randomly get error code 13 mismatch at this line highligted in red. But I just ran it 6 times and no error... Ill run it a few more times and see if I can duplicate. Thanks for taking the time to look at it.


Sub DeleteRows()
Dim n As Long
Dim nlast As Long
Dim rw As Range
Set rw = ActiveWorkbook.ActiveSheet.UsedRange.Rows
nlast = rw.Count
For n = nlast To 1 Step -1
If (Cells(n, 10).Value <> 1) Then
Cells(n, 10).EntireRow.Delete
End If
Next n

End Sub

[gimli]

Ok..take a look at this. I ran the macro and got the error again...

I saved the sheet with the values that produce the error...just run the macro when you open the sheet.

thanks

[mdmackillop]

Whilst Helper columns have their uses, it's neater to avoid them if you can find a logic. I don't see a problem with your code though.
Option Explicit

Sub DelRows()
Dim LRw As Long
Dim i As Long
Application.ScreenUpdating = False
LRw = Cells(Rows.Count, 1).End(xlUp).Row
For i = LRw To 2 Step -1
If Len(Cells(i, 1)) <> 0 And IsNumeric(Cells(i, 1)) Then
If Len(Cells(i, 2)) = 0 And Len(Cells(i, 5)) = 0 Then
Rows(i).Delete
End If
End If
Next
Application.ScreenUpdating = True


End Sub

[nepotist]

The reason why you getting the error is because the values in the Column J are dependent on the different rows. For example: Manually delete rows from bottom,Column J would show error once you delete Row 35 and this isnt accounted for in the VBA macro. Either you need to fix the formula in Column J or account for the error in the VBA code.

Also, I do not follow the logic of Column J values. If you can explain the logic, we might be able to incorporate it in the macro itself and avoid this situation. What rows need to be deleted (The logic behindit, instead of having a column j dependent, which seems to be created just for the purpose of this macro)

[gimli]

Md,

yeah..I need all the helper columns sometimes..ha

I ran your code...it does work except for deleting the header rows etc..thats why I used a helper column cause I couldnt figure out how not to.

Open this sheet and run the macro and you see what I mean..If there are no lines entered beneath a header I want the rows and headers to disappear.

thanks:think:

[mdmackillop]

Unfortunately your sample did not show that alternative

Try
Option Explicit

Sub DelRows()
Dim LRw As Long
Dim i As Long
Application.ScreenUpdating = False
LRw = Cells(Rows.Count, 1).End(xlUp).Row
For i = LRw To 2 Step -1
If Cells(i, 2) = "Qty." Then
If Application.Sum(Cells(i + 1, 2).Resize(5)) = 0 Then
Cells(i - 1, 1).Resize(8, 6).Delete xlUp
End If
End If
Next
Application.ScreenUpdating = True
End Sub

[gimli]

Nepo

Basically if you look at rows 1 thru 8 they repeat a few times down to row 39. Rows 3 thru 7 are used to enter line items. If Line 1 and 2 are used and 3,4 and 5 are not I want them deleted. Same with 9 thru 15 and so on till the end. If no lines are present I want the header rows deleted also. So the only way I could think of doing it is with a helper column. Putting a value of 1 in the row if I dont want it deleted. I know..im lame..ha

[gimli]

ah..ok...that does it! thanks!