Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

check last column of worksheet for specific text

Posted on 2013-11-13
4
Medium Priority
?
362 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 2000 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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

618 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