Results 1 to 4 of 4

Cannot delete empty columns (below header) where pasted source data had "" as result

Threaded View

  1. #1
    Registered User
    Join Date
    04-22-2020
    Location
    Texas
    MS-Off Ver
    2017
    Posts
    2

    Cannot delete empty columns (below header) where pasted source data had "" as result

    I have a macro that cleans up my data sheet in preparation to feed into another program. Part of this cleanup is to copy the source worksheet to a new worksheet then paste all as values to remove lookup values. A separate part of the macro then deletes all rows where the interior color is anything other than yellow (colorindex=6) AND there is no data below the header row. However, this is not working on columns where I previously had a lookup value that output "" when there was no value found.

    I tried adding a code to clear all cells where value was "", however that killed performance and made the macro unusable. Is there a way around this.

    These are the two blocks that are not playing well together:

    'Copy and paste as values to remove lookup references
    
        Cells.Select
        Range("A4").Activate
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    
    'Deletes non yellow interior color columns where no values are present below header
    
    Dim iiCntr As Long
    Dim rwsToCheck As Long
    
    Set ws = ActiveSheet
    rwsToCheck = 5000
    lColumn = 103
    For iiCntr = lColumn To 1 Step -1
        If WorksheetFunction.CountA(ws.Range(ws.Cells(2, iCntr), ws.Cells(rwsToCheck, iiCntr))) = 0 And Cells(1, iiCntr).Interior.ColorIndex <> 6 Then
            ws.Columns(iiCntr).Delete
        End If
    Next
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 7
    Last Post: 03-06-2019, 05:01 PM
  2. [SOLVED] Data validation "list" - "source" has too many characters
    By bee88 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-21-2015, 04:28 PM
  3. Replies: 2
    Last Post: 09-12-2013, 10:56 PM
  4. VBA Slice Setting "Show Items Delete from the Data source"
    By milobc in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-05-2012, 08:37 PM
  5. Delete rows in the result of formulas "OK" n column "J"
    By marreco in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-29-2012, 10:08 AM
  6. Recognize "formulas "result" as "typed data", through and through.
    By gandolff in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-13-2009, 01:30 PM
  7. Replies: 4
    Last Post: 02-03-2008, 05:11 PM

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