Dec 21 2018 04:29 PM
I'm new to writing macros and I would like to know can I write a macro to delete rows if column C does not contain "x". if so how do I write it?
Also could I write a macro to delete columns that contain "abc", if so how do I write it?
Dec 27 2018 01:24 PM
Yes you can. Scripts below, I am assuming you want to look for exact matches of ABC or X only and that we don't want to be case sensitive. If we don't want exact matches only "LookAt:=xlWhole" needs to change to "LookAt:=xlPart". If you want to be case sensitive then "MatchCase:=False" needs to be switched to "MatchCase:=True". If you have a header row you want to ignore then
"For iCntr = lRow To 1 Step -1" and "For iCntr = lCol To 1 Step -1" need to change to "... To 2 Step -1".
Sub DeleteNoXRows()
Dim ws As Worksheet
Dim rng As Range
Dim targetDate As Range
Dim lRow As Long
Dim iCntr As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
lRow = ws.Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row
For iCntr = lRow To 1 Step -1
Set rng = ws.Range("C" & iCntr).Find(What:="x", LookAt:=xlWhole, MatchCase:=False)
If rng Is Nothing Then
Rows(iCntr).Delete
End If
Next
End Sub
Sub DeleteABCColumns()
Dim ws As Worksheet
Dim rng As Range
Dim targetDate As Range
Dim lCol As Long
Dim lRow As Long
Dim iCntr As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
lRow = ws.Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row
lCol = ws.Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlPrevious).Column
For iCntr = lCol To 1 Step -1
Set rng = ws.Range(Cells(1, iCntr), Cells(lRow, iCntr)).Find(What:="ABC", LookAt:=xlWhole, MatchCase:=False)
If rng Is Nothing Then
Else
Columns(iCntr).Delete
End If
Next
End Sub