Copy, Paste Macro without "" results Help

RossMS

New Member
Joined
Apr 30, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,452
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.

VBA Code:
    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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,515
Office Version
  1. 365
Platform
  1. Windows
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
Excel Formula:
=SORT(FILTER('Heavy Inventory Data Sheet'!A2:R500,'Heavy Inventory Data Sheet'!Q2:Q500<>""),17,-1)
 

Forum statistics

Threads
1,175,733
Messages
5,899,159
Members
434,750
Latest member
XLPandit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top