+ Reply to Thread
Results 1 to 8 of 8

Delete Columns Based On Fill Color

Hybrid View

  1. #1
    Registered User
    Join Date
    07-13-2005
    Location
    Louisville ky
    MS-Off Ver
    2018
    Posts
    90

    Delete Columns Based On Fill Color

    I posted this on Mr Excel, but have had no luck. I have a worksheet with, say, >50 columns of data. Some of the columns are filled with red, while the remainder are not. What I would like is code that would delete all of the columns that are not filled in red. Any ideas?
    Thanks in advance.

  2. #2
    Valued Forum Contributor
    Join Date
    11-20-2012
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2010
    Posts
    597

    Re: Delete Columns Based On Fill Color

    is this going to be a recurring thing? ~50 cols shouldn't take that long to do manually?

    also, could you post an example?

  3. #3
    Registered User
    Join Date
    07-13-2005
    Location
    Louisville ky
    MS-Off Ver
    2018
    Posts
    90

    Re: Delete Columns Based On Fill Color

    Hi Scott,
    Thanks for your prompt reply. If I had, say, 75 different columns, with half and half being filled and not, that would entail quite a number of keystrokes-that is what I am trying to avoid-efficiency! Here is a simple example.
    Thanks again.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    07-13-2005
    Location
    Louisville ky
    MS-Off Ver
    2018
    Posts
    90

    Re: Delete Columns Based On Fill Color

    Scott? Any answer?

  5. #5
    Valued Forum Contributor
    Join Date
    11-20-2012
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2010
    Posts
    597

    Re: Delete Columns Based On Fill Color

    Sub rick()
    
    With ActiveSheet
        lastcol = .UsedRange.Columns(.UsedRange.Columns.Count).Column
        startcol = 1
    
        Range("A1").Select
    
        For thiscol = startcol To lastcol Step 1
    
        If Selection.Interior.Color = 255 Then Selection.EntireColumn.Delete Shift:=xlToLeft
        If Selection.Interior.Color <> 255 Then ActiveCell.Offset(, 1).Select
    
        Next thiscol
    
    End With
    End Sub

  6. #6
    Registered User
    Join Date
    07-13-2005
    Location
    Louisville ky
    MS-Off Ver
    2018
    Posts
    90

    Re: Delete Columns Based On Fill Color

    Thanks Scott. It works great except it deletes the filled columns not the unfilled columns!

  7. #7
    Valued Forum Contributor
    Join Date
    11-20-2012
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2010
    Posts
    597

    Re: Delete Columns Based On Fill Color

    trying to figure it out, columns are more difficult than rows since they have letter designations instead of numbers

  8. #8
    Valued Forum Contributor
    Join Date
    11-20-2012
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2010
    Posts
    597

    Re: Delete Columns Based On Fill Color

    sorry about that

    just change the = --> <>, and <> --->=, and it will be fixed

    Sub rick()
    
    With ActiveSheet
        lastcol = .UsedRange.Columns(.UsedRange.Columns.Count).Column
        startcol = 1
    
        Range("A1").Select
    
        For thiscol = startcol To lastcol Step 1
    
        If Selection.Interior.Color <> 255 Then Selection.EntireColumn.Delete Shift:=xlToLeft '<---this says, if the interior color is NOT equal to 255 (red), then select the entire column and delete
        If Selection.Interior.Color = 255 Then ActiveCell.Offset(, 1).Select '<---this says, if the interior color IS equal to 255 (red), then offset by one column and try again
    
        Next thiscol
    
    End With
    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