+ Reply to Thread
Results 1 to 2 of 2

[Macro] Deleting Columns Based on Multiple Conditions

  1. #1
    Registered User
    Join Date
    09-20-2006
    Posts
    1

    [Macro] Deleting Columns Based on Multiple Conditions

    What I'm Trying To Do: Use a Macro to Delete entire columns if any cell in that column contains a specific value.

    My Problem: My list of values has grown. I need a way to say "If Cell Contains "02P" OR "03P" OR "04P" OR etc..."

    Below is my code. It works great, but given the number of values I now have, I would have to edit and paste this code about 50 times. Is there a way to simplify it?
    -----------------------
    Dim rng As Range
    Dim what As String
    what = "02P"
    Do
    Set rng = ActiveSheet.UsedRange.Find(what)
    If rng Is Nothing Then

    Exit Do
    Else
    Column(rng.Column).Delete
    End If
    Loop
    ------------------------

    Thanks!

  2. #2
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394
    I would use a different approach. Try this

    Sub DeleteColumns()
    Dim myRange As Range
    Dim myDeleteColumns(256) As Boolean
    'create a string of all data that if found, will cause a column delete
    myDeleteData = "02P,03P,04P,05P"
    'use goto special to highlight all cells that contain a constant
    Cells.SpecialCells(xlCellTypeConstants, 23).Select
    Set myRange = Selection
    For Each cell In myRange
    'search to see if the cell contents is in the list
    If InStr(myDeleteData, cell.Value) Then
    'if so, then mark it for deletion
    myDeleteColumns(cell.Column) = True
    End If
    Next cell
    For x = 256 To 1 Step -1
    'delete columns from the right, so you keep integrity of column numbers
    If myDeleteColumns(x) = True Then
    Columns(x).Delete
    End If
    Next x
    End Sub

+ 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