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

  • Calculators
  • Thread starter Adel Makram
  • Start date
  • #1
612
13
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.
 

Answers and Replies

  • #2
BvU
Science Advisor
Homework Helper
14,480
3,752
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
612
13
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.5 KB · Views: 489
Last edited:
  • #4
612
13
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
phinds
Science Advisor
Insights Author
Gold Member
17,138
8,360
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.
 
  • #7
phinds
Science Advisor
Insights Author
Gold Member
17,138
8,360
Adel is the OP. The suggestion was from BvU.
OOPS. Thanks for catching that Mark & my apologies to BvU
 

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

  • Last Post
Replies
2
Views
17K
Replies
5
Views
1K
  • Last Post
Replies
2
Views
7K
Replies
8
Views
1K
Replies
3
Views
3K
Replies
3
Views
15K
Replies
17
Views
2K
  • Last Post
Replies
16
Views
4K
Top