Problems deleting row based on two adjacent cell values.

[Dave]

Hi All:

I'm using a set of forms to capture data, save it to dated workbook, and copy data to access97 using DAO. In order to not have null data in the database, I'm trying to delete rows if the values in adjacent cells C3 and D3 (C4 and D4...C6, D6) is 0. After a search of the board I found, and modified, this code (thanks Nimrod):

Option Explicit

Public Sub DelRowsbyCellValue()

Dim rw As Integer

With Worksheets("Filters")

For rw = 1 To .Range("a1:a" & Cells(65536, 1).End(xlUp).Row)

If Cells(rw, 3).Value = 0 And Cells(rw, 4).Value = 0 Then

Cells(rw, 4).EntireRow.Delete

rw = rw - 1

End If

Next

End With

End Sub


When this code runs I get an "Error 13, Type Mismatch".

Any thoughts, hints, or suggestions??

Is there a better way to do this?

By the by, the workbook hides when the forms load so the user never sees it.

Thanks in advance

Dave

Here is the original post of the above code http://www.mrexcel.com/board/viewtopic.php?topic=10744&forum=2

 

[New Poster]

For rw = 1 To .Range("a1:a" & Cells(65536, 1).End(xlUp)).Row

 

[Dave]

Thanks (dang, talk about not seeing the forest for the trees), but now I have another error:

Runtime error 1004, application defined or object defined error.

Thanks

Dave

 

[Dave]

Thanks (dang, talk about not seeing the forest for the trees), but now I have another error:

Runtime error 1004, application defined or object defined error.

Thanks

Dave

 

[Jim North]

Dave,

The "good" news is that the original posted code doesn't work either. What column will definitely have data in it for the rows you want to test?

 

[New Poster]

Sorry, should be :-

Public Sub DelRowsbyCellValue()
Dim rw As Integer
With Worksheets("Sheet1")
For rw = .Cells(65536, 1).End(xlUp).Row To 1 Step -1
If .Cells(rw, 3).Value = 0 And .Cells(rw, 4).Value = 0 Then
.Cells(rw, 4).EntireRow.Delete
End If
Next
End With
End Sub

 

[Dave]

Jim:

The data to test is in col. C3:C6 and D3:D6. C3 and D3 are a pair, C4 and D4 are a pair, etc, etc...

Gonna try New Poster's Code and let You know how that work tomorrow.....

Thanks again

Dave

 

[Jim North]

I don't expect you will have problems with New Poster's code... but just in case, what I am looking for is a column in the row that is not be blank if the row should be tested... for example, if the row is to be tested in there always a value/text (something) in column A??

 

[New Poster]

On 2002-09-12 12:21, Jim North wrote:
I don't expect you will have problems with New Poster's code... but just in case, what I am looking for is a column in the row that is not be blank if the row should be tested... for example, if the row is to be tested in there always a value/text (something) in column A??

I assumed that to be the case based on the original code.

However, the following revised code is more likely to be appropriate (it's based on the last cell with data in column C instead of column A) :-

Public Sub DelRowsbyCellValue()
Dim rw As Integer
With Worksheets("Sheet1")
For rw = .Cells(65536, 3).End(xlUp).Row To 1 Step -1
If .Cells(rw, 3).Value = 0 And .Cells(rw, 4).Value = 0 Then
.Cells(rw, 4).EntireRow.Delete
End If
Next
End With
End Sub

 

[Andrew Poulsom]

Just a thought:

Dim rw As Integer

If there are more than 32,767 used rows there will be a type mismatch. Should be:

Dim rw As Long