VBA to Delete Rows based on value of a cell

[Aggie2014]

Hi all,

As part of a bigger macro, I'm adding code that will delete rows of data if the value in column O is 0 and I can't seem to get it to function correctly. Data in column O is a dummy variable that's been created based on data in other columns and is posted as a value, rather than formula. I'm not getting any errors when I run the macro, however the cells with 0 value in column O do not get deleted.

The code that I'm using for the deletion is as follows:

For w = Range("C" & Rows.Count).End(xlUp).Row to 7 Step -1
If Range("O7" & w).Value = "0" Then
Rows(w).EntireRow.Delete
End If
Next w

Am I not seeing something that's causing it not to work properly?

 

[DanteAmor]

@DanteAmor

Thanks for the code! I've tried using autofiller, but because I have blank rows separating my data subsets, the filter would not go all the way to the bottom of the dataset, but stop at the first blank separator row. Could the proposed code be augmented to go to the bottom of the set, rather than stopping at the first blank?

Tryt this, It should work even with empty cells in column O or column A

Sub Macro1()
  Range("A7", Cells(Rows.Count, "O").End(xlUp)).AutoFilter Field:=15, Criteria1:=0
  ActiveSheet.AutoFilter.Range.Offset(1).EntireRow.Delete
  ActiveSheet.ShowAllData
End Sub

 

[Aggie2014]

So you know how you don't see some things on a big dataset until you look at it a few times or at a later time?

I just noticed that the deletion code also deletes the title rows of the data subsets that do not have anything in the dummy variable column. It's the weirdest thing, because it leave the blank rows alone, but if there is a title in column C and no data in the rest of the cells, it deletes it. These titles will be in different rows depending on the size of the dataset and need to be included. Is there a way to tweak the code not to delete these?

 

[DanteAmor]

Sorry, I'm trying to reproduce examples of what you mention and Michael's macro in post #3 and my macro in post #11 delete the data if in column O it has zero (0).
Maybe I'm missing something.
You could paste a sample of your data here where the macro is deleting the records it shouldn't.

 

[Aggie2014]

Hi,

Here's a reproduced sample of the dataset. The math doesn't work on the over/under and calculations, but the formatting and totals and subset headers are accurate. The calculated dummy variable which in the example is in column N is what I'm using to filter the data. If the value in that column is 0, then the row needs to be deleted; if it's not zero or blank then it needs to stay in.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2
3	Fund 1
4
5	Account	Description	PYE Actual	PYTD Actual	Budget	Actual	Difference	Year End Projection	Over/under	Proposed Budget	over/under	Projection Warning	Comments	Dummy Variable
6
7	1	revenue 1	10	10	10	10	10	10	10	10	10			3
8	2	revenue 2	20	20	20	20	20	20	20	20	20			4
9	3	revenue 3	30	30	30	30	30	30	30	30	30			0
10	4	revenue 4	14	14	14	14	14	14	14	14	14			0
11		***TOTAL REVENUE***	74	74	74	74	74	74	74	74	74			4
12
13		PERSONNEL SERVICES
14	1	Account 1	10	10	10	10	10	10	10	10	10			0
15	2	Account 2	20	20	20	20	20	20	20	20	20			0
16	3	Account 3	30	30	30	30	30	30	30	30	30			3
17	4	Account 4	14	14	14	14	14	14	14	14	14			4
18		***TOTAL PERSONNEL SERVICES***	74	74	74	74	74	74	74	74	74			5
19
20
21		SUPPLIES
22	1	Account 1	10	10	10	10	10	10	10	10	10			5
23	2	Account 2	20	20	20	20	20	20	20	20	20			4
24	3	Account 3	30	30	30	30	30	30	30	30	30			3
25	4	Account 4	14	14	14	14	14	14	14	14	14			0
26		***TOTAL SUPPLIES***	74	74	74	74	74	74	74	74	74			8
27
28
29		PERSONNEL CAPITAL OUTLAY
30	1	Account 1	10	10	10	10	10	10	10	10	10			0
31	2	Account 2	20	20	20	20	20	20	20	20	20			0
32	3	Account 3	30	30	30	30	30	30	30	30	30			5
33	4	Account 4	14	14	14	14	14	14	14	14	14			5
34		***TOTAL CAPITAL OUTLAY***	74	74	74	74	74	74	74	74	74			5
Sheet1

Cell Formulas
Range	Formula
C11:K11, C34:K34, C26:K26, C18:K18	C11	=SUM(C7:C10)

 

[Aggie2014]

Here's the code that's in the macro currently:

Dim lastRow2 As Long
lastRow2 - Cells(Rows.Count. "H").End(xlUp).Row
With Range("N7:N" & lastRow2)
.Formula = "=D7+E7+F7+G7+i7+k7"
.Value = .Value
End With

With Range("N7", Cells(Rows.Count, "N").End(xlUp))
.Replace 0, "#N/A", xlWhole, , False, , False, False
Columns("N").SpecialCalls(XlConstants, xlErrors).EntireRow.Delete
End With

 

[Aggie2014]

Sorry for the multiple posts: the data that's getting deleted is in rows 13, 21, and 29 in the example above.

 

[Aggie2014]

To bring this to a close, I made the macro work by hard posting values into the subset header rows to trick the macro into not deleting them. Since it's a dummy variable, the entire column gets deleted as part of the macro once the zero cells get deleted. It's not the most elegant way, but it got me through the roadblock with the macro.

Thank you all very much for your assistance and suggestions on this.

 

[Michael M]

Glad to help...?