Delete column if specific value is exist in same column

[bhandari]

We have a Data Validation list In A1 cell (ADD,REMOVE)

if the cell value is is "ADD" then do nothing
if the cell value is "REMOVE" then delete entire column

similar to "B" Column,"C" Column..etc

 

[Logit]

.
There are many ways ... here is one :

Option Explicit


Sub sbDelete_Rows_With_Specific_Data()
Dim lRow As Long
Dim iCntr As Long
Dim iCol As Long
iCol = 3  'add cols here
lRow = 100   'add rows here
For iCol = 1 To 100  'match last col number here with total cols above
    For iCntr = lRow To 1 Step -1
        If Cells(iCntr, iCol) = "REMOVE" Then ' You can change this text
            Rows(iCntr).Delete
        End If
    Next
Next
End Sub

 

[bhandari]

Your code is little tricky to me..i changed Rows to Columns In your code,it deleted the column, but its is not fulfilled my requirement, can we hide fixed columns and delete those fixed columns.
A B C D E

DELETE COLUMN	DELETE COLUMN	DELETE COLUMN	DELETE COLUMN	DELETE COLUMN
REMOVE	REMOVE	REMOVE	REMOVE	REMOVE

<tbody>
</tbody>

BLUE=If there is "REMOVE" text in A2 Cell then delete the "A" column,Similar to "B"... UP TO E
BROWN=A2,B2,C2,D2,E2 Cell Value.

Is it possible if the sheet is protected.i want to unprotect then run my code

Sub sbDelete_Rows_With_Specific_Data()


Dim lRow As Long
Dim iCntr As Long
Dim iCol As Long


iCol = 1  'add cols here
lRow = 5   'add rows here
For iCol = 1 To 5  'match last col number here with total cols above
    For iCntr = lRow To 1 Step -1
        If Cells(iCntr, iCol) = "REMOVE" Then ' You can change this text
            Columns(iCntr).Delete
        End If
    Next
Next


End Sub

this code is given in this link.i have raised a question
https://www.mrexcel.com/forum/excel...ide-column-hide-first-column.html#post5184406

i want this code to work for both hiding and deleting columns

[COLOR=#333333][FONT=Courier]Sub protect_Unprotect()

dim sh [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]As[/FONT][/COLOR][COLOR=#333333][FONT=Courier] Worksheet[/FONT][/COLOR]
[COLOR=#00007F][FONT=Courier]Dim[/FONT][/COLOR][COLOR=#333333][FONT=Courier] myPassword [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]As[/FONT][/COLOR][COLOR=#00007F][FONT=Courier]String[/FONT][/COLOR]
[COLOR=#333333][FONT=Courier]myPassword = "password"[/FONT][/COLOR]

[COLOR=#00007F][FONT=Courier]For[/FONT][/COLOR][COLOR=#00007F][FONT=Courier]Each[/FONT][/COLOR][COLOR=#333333][FONT=Courier] sh [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]In[/FONT][/COLOR][COLOR=#333333][FONT=Courier] ActiveWorkbook.Worksheets[/FONT][/COLOR]
[COLOR=#333333][FONT=Courier]sh.Protect Password:=myPassword[/FONT][/COLOR]
[COLOR=#00007F][FONT=Courier]Next[/FONT][/COLOR][COLOR=#333333][FONT=Courier] sh[/FONT][/COLOR]

[COLOR=#00007F][FONT=Courier]End[/FONT][/COLOR][COLOR=#00007F][FONT=Courier]Sub[/FONT][/COLOR]

[COLOR=#00007F][FONT=Courier]Sub[/FONT][/COLOR][COLOR=#333333][FONT=Courier] UnprotectAll()[/FONT][/COLOR]
[COLOR=#00007F][FONT=Courier]Dim[/FONT][/COLOR][COLOR=#333333][FONT=Courier] sh [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]As[/FONT][/COLOR][COLOR=#333333][FONT=Courier] Worksheet[/FONT][/COLOR]
[COLOR=#00007F][FONT=Courier]Dim[/FONT][/COLOR][COLOR=#333333][FONT=Courier] myPassword [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]As[/FONT][/COLOR][COLOR=#00007F][FONT=Courier]String[/FONT][/COLOR]
[COLOR=#333333][FONT=Courier]myPassword = "password"[/FONT][/COLOR]

[COLOR=#00007F][FONT=Courier]For[/FONT][/COLOR][COLOR=#00007F][FONT=Courier]Each[/FONT][/COLOR][COLOR=#333333][FONT=Courier] sh [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]In[/FONT][/COLOR][COLOR=#333333][FONT=Courier] ActiveWorkbook.Worksheets[/FONT][/COLOR]
[COLOR=#333333][FONT=Courier]sh.Unprotect Password:=myPassword[/FONT][/COLOR]
[COLOR=#00007F][FONT=Courier]Next[/FONT][/COLOR][COLOR=#333333][FONT=Courier] sh[/FONT][/COLOR]

[COLOR=#00007F][FONT=Courier]End [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]Sub
[/FONT][/COLOR][COLOR=#00007F][FONT=Courier]
[/FONT][/COLOR]

 

[bhandari]

I have got little bit code which is close to my requirement,but it is deleting only cell instead of column.

Sub Delete_Columns()


Dim sh As Worksheet
Dim myPassword As String
Dim i As Long
Dim Lastcolumn As Long


myPassword = "password"


For Each sh In ActiveWorkbook.Worksheets
sh.Unprotect Password:=myPassword
Next sh


Lastcolumn = Cells(1, Columns.Count).End(xlToLeft).Column
Dim r As Range
For Each r In Cells(1, 1).Resize(, Lastcolumn)
    With r.Value
        Select Case r.Value
            Case "REMOVE"
                r.Columns.Delete
            Case Else
            End
        End Select
    End With
Next


For Each sh In ActiveWorkbook.Worksheets
sh.Protect Password:=myPassword
Next sh


End Sub

 

[Logit]

.

Previously posted version had : For iCol = 1 To 100 'match last col number here with total cols above

I overlooked that line which stipulates the code will consider the first 100 columns instead of the first 3.

Changed the line to : For iCol = 1 To 3 works for the first three columns now.

All you need to do is add your code for the password.

Option Explicit




Sub sbDelete_Rows_With_Specific_Data()
Dim lRow As Long
Dim iCntr As Long
Dim iCol As Long
iCol = 3  'add cols here
lRow = 100   'add rows here
For iCol = 1 To 3 'match last col number here with total cols above
    For iCntr = lRow To 1 Step -1
        If Cells(iCntr, iCol) = "REMOVE" Then ' You can change this text
            Rows(iCntr).Delete
        End If
    Next
Next
End Sub

 

[Rick Rothstein]

I am not 100% sure of your layout, but (guessing) does this macro do what you want...

Sub DeleteREMOVE()
  Dim C As Long, LCol As Long
  LCol = Cells(1, Columns.Count).End(xlToLeft).Column
  For C = LCol To 1 Step -1
    If Cells(1, C) = "REMOVE" Then Columns(C).Delete
  Next
End Sub

 

[bhandari]

Rick Rothstein Thanks Alot
Your code is working properly.

Kindly Please solve this situation

my sheet is already protected (I know the sheet password)..i have to keep un protect then run your macro to "delete the column"

1) i want to add command button. which is useful to un protect then run your macro then protect it(back to the same position)=i mean in this sheet some of the cells are locked and editable

Sub DeleteREMOVE()
  
  Dim C As Long, LCol As Long
  Dim sh As Worksheet
  Dim myPassword As String


  myPassword = "password"

  For Each sh In ActiveWorkbook.Worksheets
  sh.Unprotect Password:=myPassword
  Next sh


  LCol = Cells(1, Columns.Count).End(xlToLeft).Column
  For C = LCol To 1 Step -1
  If Cells(1, C) = "REMOVE" Then Columns(C).Delete
  Next
  
  For Each sh In ActiveWorkbook.Worksheets
  sh.Protect Password:=myPassword
  Next sh


End Sub

 

[Rick Rothstein]

Why are you unprotecting every sheet given that the code is only working on the active sheet? You should be able to change your code to this and it should still work...

Sub DeleteREMOVE()
  
  Dim C As Long, LCol As Long

  ActiveSheet.Unprotect "password"

  LCol = Cells(1, Columns.Count).End(xlToLeft).Column
  For C = LCol To 1 Step -1
    If Cells(1, C) = "REMOVE" Then Columns(C).Delete
  Next
  
  sh.Protect "password"

End Sub

 

[bhandari]

DONE..

[COLOR=#333333]Activesheet.Protect "password"
[/COLOR]

 

[bhandari]

LCol = Cells(3, Columns.Count).End(xlToLeft).Column
For C = LCol To 20 Step -1
    If Cells(19, C) = "REMOVE" Then Columns(C).Delete
  Next

This is the last and final question for you Rick Rothstein..
"SORRY FOR THE DISTURBANCE"

is it possible to start from column number 19 to 23 then 27 to 29(only this columns have "REMOVE") in cell
Example:
R3=REMOVE
S3=REMOVE
T3=REMOVE
U3=REMOVE

AB3=REMOVE
AC3=REMOVE

this columns need to delete the entire column