How to delete column(s) with particular values in Excel?

  • Calculators
  • Thread starter Adel Makram
  • Start date
  • Tags
    Delete Excel
In summary, the conversation discusses different methods for automatically deleting columns in Excel that contain certain values in specific rows. The suggested solutions include using a macro and a manual method involving the use of the find function. The macro is seen as a more efficient and repeatable solution.
  • #1
Adel Makram
635
15
In Excel, I wish to automatically delete multiple columns that have (0) in particular row location. How to do this? After the deletion, the data array should shrink to only columns that do not have (0) at that row.
 
Computer science news on Phys.org
  • #2
Hi Adel,

Easiest is a Macro like (assuming data is in C4:I10 and the row with the criterion zeroes is Row 10):
Code:
Private Sub CommandButton1_Click()

    For iColumn = 9 To 3 Step -1
    
    If ActiveSheet.Cells(10, iColumn).Value = 0 Then
        ActiveSheet.Columns(iColumn).Delete Shift:=xlToLeft
    End If
    
    Next

End Sub
 
  • #3
BvU said:
Hi Adel,

Easiest is a Macro like (assuming data is in C4:I10 and the row with the criterion zeroes is Row 10):
Code:
Private Sub CommandButton1_Click()

    For iColumn = 9 To 3 Step -1
 
    If ActiveSheet.Cells(10, iColumn).Value = 0 Then
        ActiveSheet.Columns(iColumn).Delete Shift:=xlToLeft
    End If
 
    Next

End Sub
Hi BvU, may be I was not clear in describing what I wish to do. I attach a data that I want to operate on. In this data I wish to conditionally delete any columns that does not contain the value (1) in either row 1 or 2 and any column that contains (1) in row 1 and 2 but contain other (1) in row 4. After the deletion, only columns A and B remain.
 

Attachments

  • Snap 2017-01-01 at 18.14.26.png
    Snap 2017-01-01 at 18.14.26.png
    3.1 KB · Views: 559
Last edited:
  • #4
Adel Makram said:
Hi BvU, may be I was not clear in describing what I wish to do. I attach a data that I want to operate on. In this data I wish to conditionally delete any columns that does not contain the value (1) in either row 1 or 2 and any column that contains (1) in row 1 and 2 but contain other (1) in row 4. After the deletion, only columns A and B remain.
I found a simple solution. I highlight row 4 then use the function (find), this will find the value (1) in row 4 and then I delete all columns containing (1) leaving only columns A and B as desired.
 
  • #5
Adel Makram said:
I found a simple solution. I highlight row 4 then use the function (find), this will find the value (1) in row 4 and then I delete all columns containing (1) leaving only columns A and B as desired.
Yes, but that's a relatively tedious manual method, will need to be repeated if the data changes, and is subject to human error if repeated. Adel's suggestion of a macro is much more versatile and easily repeatably. If you need this sort of thing very often, you would do well to learn a small amount of VBA.
 
  • #6
phinds said:
Adel's suggestion...
Adel is the OP. The suggestion was from BvU.
 
  • #7
Mark44 said:
Adel is the OP. The suggestion was from BvU.
OOPS. Thanks for catching that Mark & my apologies to BvU
 

Related to How to delete column(s) with particular values in Excel?

What are the steps to delete a column with a particular value in Excel?

To delete a column with a particular value in Excel, follow these steps:

  1. Select the entire column by clicking on the column header.
  2. Click on the "Home" tab in the menu bar.
  3. Click on the "Find & Select" button and select "Replace" from the drop-down menu.
  4. In the "Find what" field, enter the particular value you want to delete.
  5. Leave the "Replace with" field blank.
  6. Click on the "Replace All" button.
  7. Click "OK" when prompted to confirm the action.
  8. The column with the particular value will now be deleted.

Can I delete multiple columns with a particular value at once in Excel?

Yes, you can delete multiple columns with a particular value at once in Excel by following these steps:

  1. Select the columns with the particular value by clicking and dragging on the column headers.
  2. Follow steps 2-8 from the previous question.
  3. All selected columns with the particular value will be deleted at once.

Is it possible to delete only specific cells with a particular value in Excel?

Yes, you can delete only specific cells with a particular value in Excel by following these steps:

  1. Select the cells with the particular value by clicking and dragging on the cells.
  2. Right-click on one of the selected cells and click on "Delete" from the drop-down menu.
  3. Select "Shift cells up" from the options and click "OK".
  4. The selected cells with the particular value will now be deleted.

Are there any keyboard shortcuts to delete a column with a particular value in Excel?

Yes, you can use the following keyboard shortcut to delete a column with a particular value in Excel:

1. Select the entire column by clicking on the column header.

2. Press the "Ctrl" and "H" keys on your keyboard simultaneously to open the "Find & Replace" dialog box.

3. In the "Find what" field, enter the particular value you want to delete.

4. Leave the "Replace with" field blank.

5. Press the "Enter" key on your keyboard to delete the column with the particular value.

Similar threads

  • Computing and Technology
Replies
9
Views
1K
Replies
27
Views
3K
  • Computing and Technology
Replies
10
Views
1K
  • Computing and Technology
Replies
24
Views
8K
  • Computing and Technology
Replies
5
Views
2K
  • Set Theory, Logic, Probability, Statistics
Replies
4
Views
1K
  • Computing and Technology
Replies
14
Views
3K
Replies
1
Views
698
  • Precalculus Mathematics Homework Help
Replies
1
Views
674
  • Linear and Abstract Algebra
Replies
4
Views
2K
Back
Top