macros

julie vandermeulen
Occasional Contributor

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?


1 Reply

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

 

Related Conversations