1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: delete all columns except some with particular headers in row 1

Discussion in 'Business Applications' started by drpagal, Jul 10, 2009.

Thread Status:
Not open for further replies.
  1. drpagal

    drpagal Thread Starter

    Joined:
    Jun 12, 2009
    Messages:
    9
    i tried looking around for a similar macro that would delete all columns except few based on the name in row 1, but all i could find is deleting rows based on condition, not deleting columns as i need and doing the additional things that i need..

    i need this macro to do couple of things, here is the breakdown:
    1. remove all columns except few based on row 1 name
    2. remove entire row based on a condition
    3. rename some titles in row 1
    4. reorder the entire sheet based on date in order from oldest to newest



    first i'll explain my excel sheet then below is the bits of code that i think is correct, just need to put it together...

    this is my excel sheet...to make things easy, i will only write out the names of the columns i would like to keep.

    Column A, row 1: Supplier
    Column B, row 1: Quantity
    Column C, row 1: UOM
    Column D, row 1: (blank) --- i also want this column removed, but has no name
    Column E, row 1: Destination Type
    Column F, row 1: Item
    Column H, row 1: Item Description
    Column I, row 1: Location
    Columns J - L -- remove
    Column M, row 1: Subinventory
    Columns N - W -- remove
    Column X, row 1: Order
    Columns Y - AP -- remove
    Column AQ, row 1: [] -- yes, this is what its called when exported from the database
    Column AR -- remove

    1. i need to remove the columns from above. I have a code that will delete the items that i dont need by writing out the column names,
    but i want it the other way around so it will delete everything except what i need (this macro below is only half of the actual length)

    Code:
    Sub ReceiveOnly()
     lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
      For delCol = lastCol To 1 Step -1
       If Cells(1, delCol) = "Rev" Then _
         Cells(1, delCol).EntireColumn.Delete
      Next
     
     lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
      For delCol = lastCol To 1 Step -1
       If Cells(1, delCol) = "Secondary Quantity" Then _
         Cells(1, delCol).EntireColumn.Delete
      Next
     
     lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
      For delCol = lastCol To 1 Step -1
       If Cells(1, delCol) = "Secondary UOM" Then _
         Cells(1, delCol).EntireColumn.Delete
      Next
     
     lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
      For delCol = lastCol To 1 Step -1
       If Cells(1, delCol) = "Person" Then _
         Cells(1, delCol).EntireColumn.Delete
      Next
     
     lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
      For delCol = lastCol To 1 Step -1
       If Cells(1, delCol) = "Locator" Then _
         Cells(1, delCol).EntireColumn.Delete
      Next
    End Sub
    
    2. i need to remove rows under Column E "Destination Type" that has the word "Inventory". .. so from a previous code i had...

    Code:
    Sub Delete()
    Columns("E:E").SpecialCells("Inventory").EntireRow.Delete 
    End Sub
    
    3. i need to rename some of the titles...

    so after process 1 above, the excel sheet should be:

    Column A, row 1: Supplier
    Column B, row 1: Quantity
    Column C, row 1: UOM
    Column D, row 1: Destination Type
    Column E, row 1: Item
    Column F, row 1: Item Description
    Column G, row 1: Location
    Column H, row 1: Subinventory
    Column I, row 1: Order
    Column J, row 1: []

    so here is what i was thinking for this...

    Code:
    ActiveSheet.
    Range("B1") = "Qty"
    Range("H1") = "Sub Inv"
    Range("I1") = "PO"
    Range("J1") = "Dock Date"
    
     
  2. drpagal

    drpagal Thread Starter

    Joined:
    Jun 12, 2009
    Messages:
    9
    any help with this would be great, thanks!
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - Solved delete columns
  1. bj nick
    Replies:
    8
    Views:
    315
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/841974

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice