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

Skidood

New Member
Joined
Jan 1, 2018
Messages
35
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>
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,252
Office Version
  1. 2013
Platform
  1. Windows
Code:
Sub CommandButton2_Click()
With Range("BX9:CA1009")
.AutoFilter Field:=1, Criteria1:="=0.00"
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
End Sub
 

Skidood

New Member
Joined
Jan 1, 2018
Messages
35
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.


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

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,857
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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?
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,252
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jan 1, 2018
Messages
35
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
ABCDEFGHIJBUBVBWBX
6
7
8Interval Interval (minutes)TC1Lethal RateFTC2Lethal RateFFT24Lethal RateF
9Row 123:54:00113.000.155113.000.155113.000.155
10Row 223:54:370:00:370.62113.000.1550.096113.000.1550.0960.096113.000.1550.096
11Row 323:55:000:00:230.38113.000.1550.059113.000.1550.0590.059113.000.1550.059
12Row 323:55:300:00:300.50113.000.1550.077113.000.1550.0770.077113.000.1550.077
13Row 423:56:080:00:380.63113.000.1550.098113.000.1550.0980.098113.000.1550.098
14Row 523:56:300:00:220.37113.000.1550.057113.000.1550.0570.057113.000.1550.057
1006Row 9978:12:000:00:300.50113.000.1550.077113.000.1550.0770.077113.000.1550.077
1007Row 9988:12:570:00:570.950.000.0000.0740.000.0000.0740.0740.000.0000.074
1008Row 9998:13:000:00:030.050.000.0000.0000.000.0000.0000.0000.000.0000.000
1009Row 10008:13:300:00:300.500.000.0000.0000.000.0000.0000.0000.000.0000.000
1010TC1TC2T23T24

<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
 
Last edited:

Skidood

New Member
Joined
Jan 1, 2018
Messages
35

ADVERTISEMENT

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

<b>Excel 2010 32 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255, 255, 255);border: 1px solid;border-collapse: collapse; border-color: rgb(182, 170, 166)"><colgroup><col width="25px" style="background-color: rgb(240, 224, 224)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(240, 224, 224);text-align: center;color: rgb(32, 17, 22)"><th></th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: rgb(32, 17, 22);text-align: center;">1005</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(204, 255, 255);;">113.00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0.155</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0.077</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(204, 255, 255);;">113.00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0.155</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0.077</td></tr><tr ><td style="color: rgb(32, 17, 22);text-align: center;">1006</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(204, 255, 255);;">113.00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0.155</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0.077</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(204, 255, 255);;">113.00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0.155</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0.077</td></tr><tr ><td style="color: rgb(32, 17, 22);text-align: center;">1007</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(204, 255, 255);;">0.00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0.000</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0.074</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(204, 255, 255);;">0.00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0.000</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0.074</td></tr><tr ><td style="color: rgb(32, 17, 22);text-align: center;">1008</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(204, 255, 255);;">0.00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0.000</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0.000</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(204, 255, 255);;">0.00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0.000</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0.000</td></tr></tbody></table><p style="width:2.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(182, 170, 166);border-top:none;text-align: center;background-color: rgb(240, 224, 224);color: rgb(32, 17, 22)">Calc</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255, 255, 255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255, 255, 255);border-collapse: collapse; border-color: rgb(182, 170, 166)"><thead><tr style=" background-color: rgb(240, 224, 224);color: rgb(32, 17, 22)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(240, 224, 224);color: rgb(32, 17, 22)">G1005</th><td style="text-align:left">=(<font color="rgb(255">F1004+F1005</font>)*D1005/2</td></tr><tr><th width="10px" style=" background-color: rgb(240, 224, 224);color: rgb(32, 17, 22)">G1006</th><td style="text-align:left">=(<font color="rgb(255">F1005+F1006</font>)*D1006/2</td></tr><tr><th width="10px" style=" background-color: rgb(240, 224, 224);color: rgb(32, 17, 22)">G1007</th><td style="text-align:left">=(<font color="rgb(255">F1006+F1007</font>)*D1007/2</td></tr><tr><th width="10px" style=" background-color: rgb(240, 224, 224);color: rgb(32, 17, 22)">G1008</th><td style="text-align:left">=(<font color="rgb(255">F1007+F1008</font>)*D1008/2</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,062
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
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

New Member
Joined
Jan 1, 2018
Messages
35
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
Code:
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
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,062
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,806
Messages
5,598,188
Members
414,218
Latest member
speedbit

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