Copy, Paste Macro without "" results Help

[RossMS]

Hello,

I have a "Heavy Inventory Data" sheet that uses equations to find all SKUs with heavy inventory based on a weeks-of-supply goal set in a cell and then calculates how much of each Heavy SKU you would have to sell through in order to get to your goal - Sell Down $.

Since this list can grow or shrink by tens of rows from week to week there are ~100 rows at the bottom that calculate out to "" - if there is nothing to calculate - to keep it clean looking. It was then supposed to copy over to one easy-to-use "Heavy Inventory" sheet where Column Q "Sell Down $" is supposed to be sorted from largest to smallest. Unfortunately, this gives me a lot of "blank" ("") rows at the top once sorted which I would like to remove automatically to make it elegant.

I tried using VBA to do this but still got the same result. I had it copy Formulas, and paste as values to somewhere else on the "Heavy Inventory Data" calculation sheet then tried to copy Constants to "Heavy Inventory" but that still brings over all the "" cells and they still sort to the top.

Dim VisRng As Range
                    With Worksheets("Heavy Inventory Data Sheet")
                    Set VisRng = .Range("A2:R500").SpecialCells(xlCellTypeFormulas)
                    VisRng.Copy
                    Worksheets("Heavy Inventory Data Sheet").Range("U2").PasteSpecial xlValues
                
            With Worksheets("Heavy Inventory Data Sheet")
            Set VisRng = .Range("U2:AL500").SpecialCells(xlCellTypeConstants)
            VisRng.Copy
            Worksheets("Heavy Inventory").Range("A4").PasteSpecial xlValues
            End With
        End With

Does anyone have suggestions on lines I can add, or a completely different code if that works better that will do a clean copy-paste of anything that does not ="".

Excel should build in a sort feature that automatically excludes (Blanks) IMO. This is a crazy frustrating little thing that really triggers OCD on the team!

Thank you in advance!

 

[AlphaFrog]

This will autofilter column R (Field 18) for blanks and then delete the filtered rows below the headers in row 1.

Change the Field number 18 to the column that has your blanks.

    Application.ScreenUpdating = False
    With Range("A1:R500")
        .AutoFilter Field:=18, Criteria1:="=" 'Filter column R (Field 18) for blanks
        .Offset(1).EntireRow.Delete
    End With
    ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True

 

[Peter_SSs]

Welcome to the MrExcel board!

Does it need to be vba?

If not, try this formula at the top-left of wherever you want the results

=SORT(FILTER('Heavy Inventory Data Sheet'!A2:R500,'Heavy Inventory Data Sheet'!Q2:Q500<>""),17,-1)