+ Reply to Thread
Results 1 to 12 of 12

VBA to find and delete columns.

  1. #1
    Forum Contributor
    Join Date
    11-17-2015
    Location
    India
    MS-Off Ver
    2013
    Posts
    338

    VBA to find and delete columns.

    Hi,
    I have sheet "Working". In this I have a column with Heading New Sheet1, New Sheet2, New Sheet3, New Sheet4, New Sheet5 and New Sheet6.

    I need a macro to find above Heading New Sheet1, New Sheet2, New Sheet3, New Sheet4, New Sheet5 and New Sheet6 in Row number 1 and if it exist then delete those columns.

    And find last filled column in the same sheet and add new 6 columns with same heading New Sheet1, New Sheet2, New Sheet3, New Sheet4, New Sheet5 and New Sheet6

    The Column heading format should be in Red font.bold and backgroung yellow colour
    Attached Files Attached Files

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    25,693

    Re: VBA to find and delete columns.

    So you don't actually want to delete the column labels, it appears you just want to clear anything that's in those columns. In which case

    Please Login or Register  to view this content.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor
    Join Date
    11-17-2015
    Location
    India
    MS-Off Ver
    2013
    Posts
    338

    Re: VBA to find and delete columns.

    Somehow.... But not exactly.

    Because these columns may anywhere in the the sheet, Between any columns or first last or in a MID. Also wanted clear contents in existing columns. Hence Wanted to find and delete these columns and insert new columns (one after one) after a last column,

  4. #4
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    2013
    Posts
    690

    Re: VBA to find and delete columns.

    Try this:

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    11-17-2015
    Location
    India
    MS-Off Ver
    2013
    Posts
    338

    Re: VBA to find and delete columns.

    hi,
    Please check the attachment the code is not running
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    495

    Re: VBA to find and delete columns.

    Does this macro do what you want...
    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    11-17-2015
    Location
    India
    MS-Off Ver
    2013
    Posts
    338

    Re: VBA to find and delete columns.

    Hi, I have given Column Heading Starting with "New Sheet" just for an example.

    Actuall I have 10 columns with below headings. And more can be added / deleted in future. Can you provide your accordingly.


    No_Annualised_Claims
    No_Annualised_Claims_With_IBNR
    Annualised_Claim_Amount
    Annualised_Claim_Amount_With_IBNR
    Age_Band_1
    Age_Band_2
    Age_Band_3
    Age_Band_4
    Age_Band_5
    Amount_Band

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    14,966

    Re: VBA to find and delete columns.

    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    11-17-2015
    Location
    India
    MS-Off Ver
    2013
    Posts
    338

    Re: VBA to find and delete columns.

    Dear Jindon,
    I tried it on sample file. It is not deleting all existing column with same heading.
    at final result only 10 columns to be there after running the code. that too it start from the last column
    Attached Files Attached Files

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    14,966

    Re: VBA to find and delete columns.

    Add/remove the item in myList.
    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    11-17-2015
    Location
    India
    MS-Off Ver
    2013
    Posts
    338

    Re: VBA to find and delete columns.

    thats perfect

  12. #12
    Valued Forum Contributor
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    495

    Re: VBA to find and delete columns.

    Quote Originally Posted by saravanan1981 View Post
    No_Annualised_Claims
    No_Annualised_Claims_With_IBNR
    Annualised_Claim_Amount
    Annualised_Claim_Amount_With_IBNR
    Age_Band_1
    Age_Band_2
    Age_Band_3
    Age_Band_4
    Age_Band_5
    Amount_Band
    Question... are the above headers the only headers that have red bold font in a yellow filled cell? If so, here is another macro that you can consider...
    Please Login or Register  to view this content.
    Note: If those headers are the only header with yellow fill, then you can remove the two lines of code I highlighted in red above.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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