EZ question....how to specify more than one column in the range criteria ???

[Skidood]

Hi again, hope everyone is well.....how can add a few more columns to the range below?



Sub CommandButton2_Click()
With Range("BX9:BX1009")
.AutoFilter Field:=1, Criteria1:="=0.00"
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
End Sub</pre>

 

[My Aswer Is This]

Sub CommandButton2_Click()
With Range("BX9:CA1009")
.AutoFilter Field:=1, Criteria1:="=0.00"
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
End Sub

 

[Skidood]

nope...but thanks...Guess I should have mentioned that the additional columns I want to add are not right beside each other...so I have to essentially cherry-pick the columns I want to include.

Sub CommandButton2_Click()
With Range("BX9:CA1009")
.AutoFilter Field:=1, Criteria1:="=0.00"
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
End Sub

 

[MARK858]

Skidood, can you please describe in more detail exactly what you are trying to do as what M.A.I.T. has posted will still filter on column B but as you are deleting the entire row are you trying to use the same criteria on multiple columns or something else?

 

[My Aswer Is This]

You need to explain in more detail what your wanting to do.

Your wanting to filter for 0.00 on what column or columns?

 

[Skidood]

I want the macro to delete any row between row 9 and 1009 where there is a 0.00 in Columns E, H, K, N, Q etc etc.
There will end up being multiple rows that need to be deleted based on the above, and they will all be consecutive rows, partway down the sheet.
In other words, the sheet will initially have numbers running down the columns that I am specifying, but at some point as you go down the column, the cell's data will switch to 0.00...and at that point, I want all those rows deleted.

Excel 2010 32 bit

	A	B	C	D	E	F	G	H	I	J	BU	BV	BW	BX
6
7
8			Interval	Interval (minutes)	TC1	Lethal Rate	F	TC2	Lethal Rate	F	F	T24	Lethal Rate	F
9	Row 1	23:54:00			113.00	0.155		113.00	0.155			113.00	0.155
10	Row 2	23:54:37	0:00:37	0.62	113.00	0.155	0.096	113.00	0.155	0.096	0.096	113.00	0.155	0.096
11	Row 3	23:55:00	0:00:23	0.38	113.00	0.155	0.059	113.00	0.155	0.059	0.059	113.00	0.155	0.059
12	Row 3	23:55:30	0:00:30	0.50	113.00	0.155	0.077	113.00	0.155	0.077	0.077	113.00	0.155	0.077
13	Row 4	23:56:08	0:00:38	0.63	113.00	0.155	0.098	113.00	0.155	0.098	0.098	113.00	0.155	0.098
14	Row 5	23:56:30	0:00:22	0.37	113.00	0.155	0.057	113.00	0.155	0.057	0.057	113.00	0.155	0.057
1006	Row 997	8:12:00	0:00:30	0.50	113.00	0.155	0.077	113.00	0.155	0.077	0.077	113.00	0.155	0.077
1007	Row 998	8:12:57	0:00:57	0.95	0.00	0.000	0.074	0.00	0.000	0.074	0.074	0.00	0.000	0.074
1008	Row 999	8:13:00	0:00:03	0.05	0.00	0.000	0.000	0.00	0.000	0.000	0.000	0.00	0.000	0.000
1009	Row 1000	8:13:30	0:00:30	0.50	0.00	0.000	0.000	0.00	0.000	0.000	0.000	0.00	0.000	0.000
1010							TC1			TC2	T23			T24

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Calc​

 

[Skidood]

Or, I would also be OK with a macro that will disable the calculation of the formula which resides in the 2nd cell after the green column. The formula is returning a value that I want deleted/removed.
In other words, in cell E1007, the number is 0.00 whereas all the cells above it are 113...... ONLY when the number is 0.00, I want the formula in cell G1007 to be disabled or deleted.
Now, what makes it even more challenging is that this macro needs to cover columns E, H, K, N etc etc (every 3rd column) right up to column BZ

Excel 2010 32 bit
	E	F	G	H	I	J
1005	113.00	0.155	0.077	113.00	0.155	0.077
1006	113.00	0.155	0.077	113.00	0.155	0.077
1007	0.00	0.000	0.074	0.00	0.000	0.074
1008	0.00	0.000	0.000	0.00	0.000	0.000
Calc

Cell Formulas
Range	Formula
G1005	=(F1004+F1005)*D1005/2
G1006	=(F1005+F1006)*D1006/2
G1007	=(F1006+F1007)*D1007/2
G1008	=(F1007+F1008)*D1008/2

 

[Fluff]

How about

Sub CommandButton2_Click()
   Dim Cnt As Long
   For Cnt = 5 To 78 Step 3
      With Cells(9, Cnt).Resize(1000)
         .AutoFilter Field:=1, Criteria1:="=0.00"
         .SpecialCells(xlCellTypeVisible).EntireRow.Delete
      End With
   Next Cnt
End Sub

 

[Skidood]

I will try that when I come back from the doctors office but can you explain what this line of code from your solution does?

For Cnt = 5 To 78 Step 3

How about

Sub CommandButton2_Click()
   Dim Cnt As Long
   For Cnt = 5 To 78 Step 3
      With Cells(9, Cnt).Resize(1000)
         .AutoFilter Field:=1, Criteria1:="=0.00"
         .SpecialCells(xlCellTypeVisible).EntireRow.Delete
      End With
   Next Cnt
End Sub

 

[Fluff]

Cnt is a counter which starts at 5 & ends on 78 but the Step 3 means it adds 3 every time, so it will be 5,8,11,14 etc
Cnt is then used in Cells(9,cnt) where it represents the column number.