asked on

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

Harry Lee

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

vbaabv

ASKER

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.

ASKER CERTIFIED SOLUTION

Harry Lee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vbaabv

ASKER

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 !