+ Reply to Thread
Results 1 to 12 of 12

Macro to delete certain column cells except specified headers

  1. #1
    Registered User
    Join Date
    07-11-2012
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    69

    Macro to delete certain column cells except specified headers

    Hi everyone,

    I am trying to create a macro which deletes a column aside from those titles specified in a header. However, I would like to delete everything in that column only from Row 9 and down (I would like to leave all the information above row 9 untouched).

    For example, the headers I want to keep are (Dog, Cat, Cow, Sheep, Duck)

    Column A B C D E F

    Row 9 Dog Cat Cow Sheep Table Duck

    I would like to delete all of column E, but only from row 9 and below. Any information I have above row 9 would not be deleted.

    Any help would be great! I tried using a macro that was suggested here by MarvinP:

    http://www.excelforum.com/excel-prog...d-headers.html

    It works great, but deletes everything in the column, and I would like to delete only information from row 9 and below.

    Thanks!

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Macro to delete certain column cells except specified headers

    If Marvin's code works for you, here is the amendment(Not a lot-only change 1 to 9)

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    07-11-2012
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Macro to delete certain column cells except specified headers

    Hi,

    Thanks for your help! I've tried changing the 1's to 9, but the code still deletes everything in the column and information above row 9 still gets deleted. I'm thinking maybe there is a way to add a range that is to be deleted for EntireColumn.Delete instead of the whole column being deleted?

    Thanks

  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Macro to delete certain column cells except specified headers

    Have you change 9 twice in the code?

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    07-11-2012
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Macro to delete certain column cells except specified headers

    Yup.

    I've changed 9 3 times in the code.

    Sub DelColumnNotInList()
    Dim LastCol As Double
    Dim ColCtr As Double
    LastCol = Cells(9, Columns.Count).End(xlToLeft).Column
    For ColCtr = 1 To LastCol
    If InStr("Word Pay Cost Volume Rank Type", Cells(9, ColCtr).Text) = 0 Then
    Cells(9, ColCtr).EntireColumn.Delete
    ColCtr = ColCtr - 1
    End If
    Next ColCtr
    End Sub

    It reads the header at row 9, but still continues to delete the entire column, rather than deleting everything from row 9 and below only.

  6. #6
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Macro to delete certain column cells except specified headers

    Okay! Do you have a sample data? The attached does not have any data, except the header

  7. #7
    Registered User
    Join Date
    07-11-2012
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Macro to delete certain column cells except specified headers

    Here you go. I would like row 9 and above untouched so the shape of the boxes would be unchanged.

    Thanks!


    DelNamedColumns.xlsm

  8. #8
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Macro to delete certain column cells except specified headers

    Thanks,
    "I would like to delete all of column E, but only from row 9 and below. Any information I have above row 9 would not be deleted."
    What is the condition, or citeria for deleting column E cells? You have 9 columns (A-J)

  9. #9
    Registered User
    Join Date
    07-11-2012
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Macro to delete certain column cells except specified headers

    Sorry, ignore the request for removing column E.

    Basically I would like any header names in Row 9 that is not "Word Pay Cost Volume Rank Type" to have its data below row 9 to be deleted.

    The effect would be something similar to something as if you highlighted B9:B24, F9:F24, G9:G24, right clicked and clicked delete and shift cells left. Everything gets shifted below row 9, but information above row 9 stays the same.

  10. #10
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Macro to delete certain column cells except specified headers

    This code hopefully should do. If you do wish to delete rows 9 to 24, you could change this line of code

    Please Login or Register  to view this content.
    in to

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    07-11-2012
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Macro to delete certain column cells except specified headers

    Thank you so much for your help! This was very useful!

  12. #12
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Macro to delete certain column cells except specified headers

    You are welcome!
    If you are happy with the solution provided, please close this thread as solved. On the top- right hand side of this page-"Thread Tools", select solved from the drop down menu

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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