Formula To Delete Certain Cells ?????

CARBOB

Well-known Member
Joined
Jun 6, 2005
Messages
1,870
I want to delete the all cells from C24:D4469 that has a zero in the Col D D, but the cell in Col C has to be deleted also. Ex C24:D30, delete, skip C31:D32; delete C33:D53, Etc. Can this be done with a formula? Thanks for all suggestions.
FL CASH 3 DIGITS SKIPS.xls
CDEF
24EOELHLIOI000#NUM!
25OEELHHIOO000#NUM!
26EEOHHHIIO000#NUM!
27EEOHHLOOI000#NUM!
28EOEHHHIOI000#NUM!
29OEEHHLOOI000#NUM!
30OOEHHLOOI000#NUM!
31EOOHHLOOI0224542454
32EEEHHHOII031016800.5
33EEEHLLOOI000#NUM!
34EOEHHHIOO000#NUM!
35EOEHLHIII000#NUM!
36EOEHLLOII000#NUM!
37OOEHLLOOI000#NUM!
38OEOLHLIOI000#NUM!
39EOOHHHOOO000#NUM!
40EOOHLHIIO000#NUM!
41OOOLLLOOI000#NUM!
42OOOLLHIIO000#NUM!
43OOOHLLOIO000#NUM!
44EEEHHHOOO000#NUM!
45EEOHLLOII000#NUM!
ODDEVEN-HIGHLOW-INOUT SKIPS (2)
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
CARBOB,
The answer to your literal question is: no. You can't delete a cell's contents with a formula.
You can however make a formula return a blank. Is that what you had in mind, or are you actually thinking of a vba routine to truely delete a ranges contents?
 
Upvote 0
CARBOB,
The answer to your literal question is: no. You can't delete a cell's contents with a formula.
You can however make a formula return a blank. Is that what you had in mind, or are you actually thinking of a vba routine to truely delete a ranges contents?

Then I will need a Macro. Thanks for responding.
 
Upvote 0
This code will delete all rows where the value in column D = zero.
Code:
Sub DeleteZeroRows()
Dim CheckRange As Range, c As Range
    Set CheckRange = Range("D1:D" & _
    Range("D65536").End(xlUp).Row - 2)
    For Each c In CheckRange
        If c.Value = 0 Then c.EntireRow.Delete
    Next c
End Sub
 
Upvote 0
This code will delete all rows where the value in column D = zero.
Code:
Sub DeleteZeroRows()
Dim CheckRange As Range, c As Range
    Set CheckRange = Range("D1:D" & _
    Range("D65536").End(xlUp).Row - 2)
    For Each c In CheckRange
        If c.Value = 0 Then c.EntireRow.Delete
    Next c
End Sub

Datsmart,thanks for responding. I'm not a macro type person. It looks as your code won't work since Col C has no zeroes.
 
Upvote 0
c is a variable which John's code declares as a Range at the beginning of the code. In it's simplest term a range, when not specified as otherwise, is each cell in an group of cells.

It therefore looks, in the other range that he has specified 'CheckRange', at each cell and evaluates it to see if it equals zero. If so it deletes the entire row.

Dom
 
Upvote 0
Thanks for explaining Dom. I just got back to this thread.

CARBOB, did you try the code?

Here are some suggestions on how to use it.

To use the posted code;
Start the Visual Basic Editor (via Menu Tools, Macro, Visual Basic Editor, or press ALT+F11).
On the Insert menu in the VBE, click Module. (if necessary)
In the module (the white area at the right), paste your code
Note: All Macros start with "Sub MacroName()" and End with "End Sub"

How to create a button and assign a macro to it:
If you don't already have the "Forms" toolbar active, click on Tools>Customize>Toolbars and check "Forms". The toolbar will appear on your screen

Click on the Button icon, and drag out a button on the screen. When you release the left mouse button, an "Assign Macro" dialog box will appear. Highlight whatever macro you want, and click OK. The macro will now be assigned to that button.

You can also run the code by hitting Alt-F8 and selecting the macro name and clicking Run.
 
Upvote 0
Datsmart:

When using a loop to delete cells or rows, you want to move backward through the range, not forward.

CARBOB:

You only said you wanted to delete the values in Column C and D, did you want to leave the rest of the row intact or delete the entire row?

What is the exact range you want to check starting at D2? Or D24 as in your example? Do you have a header row?
 
Upvote 0
Datsmart:

When using a loop to delete cells or rows, you want to move backward through the range, not forward.

CARBOB:

You only said you wanted to delete the values in Column C and D, did you want to leave the rest of the row intact or delete the entire row?

What is the exact range you want to check starting at D2? Or D24 as in your example? Do you have a header row?

D24! Delete only the Cells in Col C & Col D. No header row.
 
Upvote 0
Does this work for you?

Code:
Sub DeleteZeros()
Dim x As Long, y As Long
x = Range("D" & Rows.Count).End(xlUp).Row
For y = x To 24 Step -1
    If Range("D" & y) = 0 Then Range("C" & y).Resize(, 2).Delete xlShiftUp
Next y
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

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
Back
Top