Solved

check last column of worksheet for specific text

Posted on 2013-11-13
4
357 Views
Last Modified: 2013-11-13
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
0
Comment
Question by:vbaabv
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39646154
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
 

Author Comment

by:vbaabv
ID: 39646260
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
 
LVL 12

Accepted Solution

by:
Harry Lee earned 500 total points
ID: 39646271
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
 

Author Closing Comment

by:vbaabv
ID: 39646397
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question