Problems deleting row based on two adjacent cell values.

Dave

New Member
Joined
Mar 13, 2002
Messages
25
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Dave

New Member
Joined
Mar 13, 2002
Messages
25
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

New Member
Joined
Mar 13, 2002
Messages
25
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
791

ADVERTISEMENT

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

New Member
Joined
Aug 22, 2002
Messages
22
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

New Member
Joined
Mar 13, 2002
Messages
25

ADVERTISEMENT

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

MrExcel MVP
Joined
Jun 20, 2002
Messages
791
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

New Member
Joined
Aug 22, 2002
Messages
22
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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
 

Forum statistics

Threads
1,144,310
Messages
5,723,632
Members
422,505
Latest member
Noar33

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top