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

 

[eliW]

Hi Dave,
Just an idea-
You must not use macro here deleting rows, it cuold be handled easily by Advanced Filter:

Filter your data on a criteria which contains formula checking if the 2 adujcent cells are not empty (TRUE), and copying the filtered data to another location.

No macro
No delete rows

Just a new approach,

Eli

 

[Dave]

New Poster:

Code works great with the addition of:

worksheets("Filters").select

for some reason it looks like I have to select each sheet that I need to run this code on, No big deal though

Thanks for the Help


Jim:

I try to put some value in very cell, ran into too many problems with the dreaded "is it a space or a null" (see, I can learn, no matter what the wife says


Andrew:

There's only 4 rows of data and 2 header rows on the sheet, but Thanks for the info! That could come in handy

Eli:

Thanks for the filter suggestion, but I would rather hide and secure everything that I can and I'm not sure I can secure the filtering (some folks around here can break an anvil with a rubber hammer).

Again, Thanks for the help and suggestions. With a bit of luck, and ALOT of help from the people on this board, I should have this project finished in...oh say, 30 or 40 more years

Thanks again

Dave

 

[Dave]

New Poster:

Code works great with the addition of:

worksheets("Filters").select

for some reason it looks like I have to select each sheet that I need to run this code on, No big deal though

Thanks for the Help


Jim:

I try to put some value in very cell, ran into too many problems with the dreaded "is it a space or a null" (see, I can learn, no matter what the wife says


Andrew:

There's only 4 rows of data and 2 header rows on the sheet, but Thanks for the info! That could come in handy

Eli:

Thanks for the filter suggestion, but I would rather hide and secure everything that I can and I'm not sure I can secure the filtering (some folks around here can break an anvil with a rubber hammer).

Again, Thanks for the help and suggestions. With a bit of luck, and ALOT of help from the people on this board, I should have this project finished in...oh say, 30 or 40 more years

Thanks again

Dave