- #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.
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.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
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.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.
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.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.
Adel is the OP. The suggestion was from BvU.phinds said:Adel's suggestion...
OOPS. Thanks for catching that Mark & my apologies to BvUMark44 said:Adel is the OP. The suggestion was from BvU.
To delete a column with a particular value in Excel, follow these steps:
Yes, you can delete multiple columns with a particular value at once in Excel by following these steps:
Yes, you can delete only specific cells with a particular value in Excel by following these steps:
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.