check last column of worksheet for specific text

I am trying to check the last column in a worksheet (I do not know which column is the last column, so I also have to determine that) for the text 'yes', and then delete the entire row if the text is not 'yes'.  So far, I have the following code but do not know what to do where the '...' is:

Sub LastColumnCheckSig()
Dim rg As Range
Dim nCols As Long
Application.ScreenUpdating = False
Set rg = ActiveSheet.UsedRange
nCols = rg.Columns.Count
        If ...
       Then  .EntireRow.Delete
        End If
    Next
End Sub
vbaabvResearch ScientistAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Harry LeeConnect With a Mentor Commented:
Please re copy the macro from the above post.

Or go into the macro to make sure the "*Yes*" and "*yes*" is read "Yes" and "yes". (Should not have the * in them.

I have corrected the post, and you may have copy the code before I made the correction.
0
 
Harry LeeCommented:
You can use the following code

Sub LastColumnCheckSig()
Dim LsRw As Long, LsClmn As Long, Rw As Long

LsClmn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
LsRw = ActiveSheet.Cells(Rows.Count, LsClmn).End(xlUp).Row

For Rw = LsRw To 2 Step -1
    If ActiveSheet.Cells(Rw, LsClmn) <> "Yes" And ActiveSheet.Cells(Rw, LsClmn) <> "yes" Then
        ActiveSheet.Cells(Rw, LsClmn).EntireRow.Delete
    End If
Next
End Sub

Open in new window

0
 
vbaabvResearch ScientistAuthor Commented:
Thank you HarryHYLee for your script.
However, it seemed to delete all rows of my test sheet and there many rows where the last column was 'no'. I think that was my fault because I should have more specific that it is lower case text. The column will be either 'yes' or 'no'.

      Also, I did not mention in my question, but I want to check the 13th column (column M) for the text for 'yes' (or 'no'), then every 12 columns after that until the end. The last column will be a column checked. So, after column M, I need to check column Y then column AK and every 12 after that until the end for the presence of a 'yes' (or you could also use 'no', because it will be either 'yes' or 'no').
If all checked columns have 'no', then delete the entire row.
0
 
vbaabvResearch ScientistAuthor Commented:
Thank you HarryHKLee,

    The correction solved the problem and that was what I was originally asking. With your help, I was able to solve my additional question as well.

Thank you very much !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.