+ Reply to Thread
Results 1 to 6 of 6

Check Column for text then clear cell contents of cell if found

  1. #1
    Forum Contributor
    Join Date
    07-31-2009
    Location
    Lincoln, USA
    MS-Off Ver
    Excel 2007 and excel 2010
    Posts
    134

    Check Column for text then clear cell contents of cell if found

    Hello and thank you in advanced.

    this one should be simple and I've compared a few other examples online without luck.

    What I'm trying to do.
    - have a large report (100k lines) with a random word "adherence" in column b randomly throughout the report.
    - I want to check column B and if the word adherence is in the cell to clear contents, not delete the cell.

    I've tried this but I know I'm missing something small.


    Please Login or Register  to view this content.
    Hate to post on something so trivial but it's how you learn.

    thanks.
    Last edited by fireguy7; 06-01-2018 at 12:19 PM.

  2. #2
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,805

    Re: Check Column for text then clear cell contents of cell if found

    Hello
    Try this code

    Please Login or Register  to view this content.
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    9,901

    Re: Check Column for text then clear cell contents of cell if found

    Another option to remove the cell contents
    Please Login or Register  to view this content.

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,124

    Re: Check Column for text then clear cell contents of cell if found

    Please Login or Register  to view this content.
    I normally don’t jump in on a post unless there is a sample workbook attached, but this one is simple enough.

    Lesson #1 for VBA: Option Explicit – see below

    It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:
    Please Login or Register  to view this content.
    This option requires all variables to be declared and will give a compiler error for undeclared variables.

    You also might want to uncheck the box that says Auto Syntax Check.

    Lesson #2 for VBA: always tell Excel where you want to be. Your idea of active workbook, active sheet or active cell might not be the same as Excel’s idea of where it thinks it is.

    There are two ways to specify a sheet in VBA. I call them “By String” and “By Pointer” – these are not official names.

    By string is simple. Sheets(“Sheet Name”) means, use the sheet called Sheet Name.

    By pointer takes an initial first step. You have to set a “pointer” to the sheet.
    Please Login or Register  to view this content.
    From there on out you can use sh instead of Sheets(“Sheet Name”). Not only does this save typing, but sh can be re-set to point to another sheet or even to loop though sheets.

    Also the pointer knows which workbook it belongs to. This is very handy if you are working with multiple workbooks.

    Finally, if you set the pointer at the beginning of the code and later decide you need to change the name of the sheet, you only have to change one line of code and don’t have to go hunting for everywhere you use the sheet name.

    So although both methods work, I recommend using by pointer.

    Please Login or Register  to view this content.
    This is “standard” code for finding the last row with data in a column (in this case column B). It’s the VBA equivalent of going to the bottom of the spreadsheet and pressing CTRL-Up-Arrow.

    The rest of the code loops from row 1 to whatever LRow is and checks for the string “adherence.”

    Note that the value will only match if the strings match exactly (in this case all lower case). If you are not sure, you can “force” the comparison.

    Please Login or Register  to view this content.
    Now if you are looking for cells that contain adherence but also have something else on them, then you need to use something else such as the Like operator.

    There are several ways to address a cell:

    Sh.Range(“B” & i)
    Sh.Cells(I, “B”)
    Sh.cells (I,2)

    The one best to use depends on the situation.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  5. #5
    Forum Contributor
    Join Date
    07-31-2009
    Location
    Lincoln, USA
    MS-Off Ver
    Excel 2007 and excel 2010
    Posts
    134

    Re: Check Column for text then clear cell contents of cell if found

    Thanks everyone! 3 good example and extra information to understand the formula's as well. This way I can learn and eventually help others. Keep up the good work it's really appreciated!

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    9,901

    Re: Check Column for text then clear cell contents of cell if found

    Glad we could help & thanks for the feedback

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA to clear contents of the cell in a column of data in a cell starts with specific word.
    By winmaxservices1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-03-2015, 08:25 AM
  2. [SOLVED] VBA Help needed to clear cell contents if the Column “B” cell is blank
    By krjoshi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-19-2014, 04:54 AM
  3. [SOLVED] move down column and for every blank cell, clear contents of cell to the left
    By hopefulhart in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-01-2014, 11:23 AM
  4. Delete rows or Clear contents below a specific word is found in Column A
    By kishoremcp in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-17-2012, 04:08 PM
  5. Macro that will clear contents of cell based on format of text in adjacent cell
    By judasdac in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-12-2012, 01:56 AM
  6. Check for data in cell. If found paste text into same row another column
    By Buddy7 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-11-2011, 02:38 PM
  7. Clear cell contents by text
    By Rick_Stanich in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-25-2008, 11:18 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1