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
DELETE COLUMN | DELETE COLUMN | DELETE COLUMN | DELETE COLUMN | DELETE COLUMN |
REMOVE | REMOVE | REMOVE | REMOVE | REMOVE |
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
[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]
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
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
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
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
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
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