Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

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

  1. Jan 1, 2017 #1
    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.
     
  2. jcsd
  3. Jan 1, 2017 #2

    BvU

    User Avatar
    Science Advisor
    Homework Helper
    Gold Member

    Hi Adel,

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

    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
     
  4. Jan 1, 2017 #3
    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.
     

    Attached Files:

    Last edited: Jan 1, 2017
  5. Jan 1, 2017 #4
    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.
     
  6. Jan 1, 2017 #5

    phinds

    User Avatar
    Gold Member
    2016 Award

    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. Jan 1, 2017 #6

    Mark44

    Staff: Mentor

    Adel is the OP. The suggestion was from BvU.
     
  8. Jan 1, 2017 #7

    phinds

    User Avatar
    Gold Member
    2016 Award

    OOPS. Thanks for catching that Mark & my apologies to BvU
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook

Have something to add?
Draft saved Draft deleted



Similar Discussions: How to delete column(s) with particular values in Excel?
Loading...