Avatar of route217
Flag for United Kingdom of Great Britain and Northern Ireland

asked on 

Macro to delete blank rows and run from drop down list

Hi Experts Using Excel 2013

See attached file

I want the macro to run from the drop down in sheet “Master” cell B2. i have the following code in the Worksheet module

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$2" Then
        Call CopyandPasteValues
    End If
End Sub

The following copy and paste macro works fine I believe See below…
Sub CopyandPasteValues()

    Sheets("Unique List").Range("$A$1:$A:$A$3500").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                                                          :=False, Transpose:=False
    Application.CutCopyMode = False
    Sheets("Unique List").Range("$A$1:$A$3500").RemoveDuplicates Columns:=Array(1, 2), _

End Sub

Open in new window

But need to add function to remove blank rows from the data set, not sure how to integrate this part..

well this all work...
Microsoft Excel

Avatar of undefined
Last Comment
Subodh Tiwari (Neeraj)

8/22/2022 - Mon