+ Reply to Thread
Results 1 to 11 of 11

Delete specific columns and sheet

  1. #1
    Forum Contributor
    Join Date
    02-07-2011
    Location
    Bhutan
    MS-Off Ver
    Excel 2007
    Posts
    244

    Delete specific columns and sheet

    Hi Friends,

    I was wondering if somebody could help me out with a VBA code that could delete specific columns and all the sheets except one from all the files in a folder.
    For example I have a folder with more than 50 files, with multiple sheets, but I want all the sheets to be deleted except "FinalData" in each file. Now in this sheet "FinalData" I want to delete the Columns named "S-Unit", "C-Unit", "V-Unit", "P-Unit", "Lost-Unit", "***-Unit", "M-Unit", "HIS-Unit", "Local-Unit", "Local2-Unit". then save and close.
    It will be great if somebody could please help me out.
    Thanks in advance,
    Best regards,me.

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Delete specific columns and sheet

    For now test this macro for sheets deleting
    Please Login or Register  to view this content.
    If solved remember to mark Thread as solved

  3. #3
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,186

    Re: Delete specific columns and sheet

    Hi, patel45,

    donīt you think it would be a good idea to set
    Please Login or Register  to view this content.
    at the beginning of the code and
    Please Login or Register  to view this content.
    at the end - not every user has the warnings disabled.

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  4. #4
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Delete specific columns and sheet

    many others details are missing, sorry, as you know, i'm not a good programmer

  5. #5
    Forum Contributor
    Join Date
    02-07-2011
    Location
    Bhutan
    MS-Off Ver
    Excel 2007
    Posts
    244

    Re: Delete specific columns and sheet

    Hi Patel,
    Thanks mate , the code worked and I have deleted all the sheets, can you please help me with the columns also. and thanks HaHoBe, I added the line you provided and everything was fine.
    Thanks friends, you all are amazing !
    Best regards,me

  6. #6
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,186

    Re: Delete specific columns and sheet

    Hi, fatalcore,

    as long as the items only appear once you may utilize something like this:
    Please Login or Register  to view this content.
    Code works on the active sheet and may either be implemented into or called from within the other routine.

    Ciao,
    Holger

  7. #7
    Forum Contributor
    Join Date
    02-07-2011
    Location
    Bhutan
    MS-Off Ver
    Excel 2007
    Posts
    244

    Re: Delete specific columns and sheet

    Thanks Holger,

    I called the code in the above code by Patel and it worked like a charm ! thanks mate :D

    But I have just realized another big problem, sorry I should have expected this earlier. can you please help me out.

    Actually all my file name starts with actual user name then a space and then some junk values like for Example
    1> ADAS0456 xyy478ejhn kujadikjfjk.xlsx
    2> AWISTLER0566 asdlklk 03408588 kjdsf.xlsx
    3> JJUNE0387 kjkj45mjdkf 48799857kjf.xlsx

    what I want is after the columns are deleted can you please replace the tab name "User" to the User name like "ADAS0456" , "AWISTLER0566" , "JJUNE0387" etc. So that i can understand which user I am specifically looking.
    It will be a great help mate.

    Best regards,
    Me.

  8. #8
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,186

    Re: Delete specific columns and sheet

    Hi, fatalcore,

    from what I understand the code for renaming the remaining sheet may look something like this:
    Please Login or Register  to view this content.
    In conjunction with the code to delete columns it may look like
    Please Login or Register  to view this content.
    Ciao,
    Holger

  9. #9
    Forum Contributor
    Join Date
    02-07-2011
    Location
    Bhutan
    MS-Off Ver
    Excel 2007
    Posts
    244

    Re: Delete specific columns and sheet

    Waoo Holger,
    That's exactly what I wanted ! all I can say Brilliant my Friend
    My friends, just another query I just wanted to know is there any code that can collate the all the tabs to a single file. I mean afterward if I want to make a single file with all the user tabs. that will be a great help.
    thanks mate thanks a ton for your help.
    best regards,me

  10. #10
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,186

    Re: Delete specific columns and sheet

    Hi, fatalcore,

    please have a look at Merge data from all workbooks in a folder (1), Merge data from all workbooks in a folder (2) or WBs to 1 Sheet. Or you just add the according lines of code when having renamed the tab (first opening a (new) workbook, set up the (new) worksheet (both as objects which may be used later in code without switching between workbooks) and then copy the data either below or next to each other).

    Ciao,
    Holger

  11. #11
    Forum Contributor
    Join Date
    02-07-2011
    Location
    Bhutan
    MS-Off Ver
    Excel 2007
    Posts
    244

    Re: Delete specific columns and sheet

    Thanks mate ! :D
    Best regards,me
    marking this thread as complete.

+ 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