Display a Column of Values Based on Cell Contents

[meppwc]

I have a spreadsheet with 2 worksheets. One worksheet (Data Export) has nothing but raw exported data. The other worksheet (Summary) is information calculated from the Data Export worksheet.

What I am trying to do on the Summary worksheet, is list any nonBlank value found in column W of the Data Export.
I guess you could say it would look like a table on the Summary page. And that table would only show nonBlank values collected from Data Export column W

Is there anyway to do that?

 

[ghrain22]

You could probably do that with an Advanced Filter would do that.

Filter by using advanced criteria - Excel

 

[Momentman]

You can copy the data and from the paste special window, select - constants (that way it skips the blanks)

You can then paste the data in your desired location

The advanced filter should also work

 

[NeonRedSharpie]

Sub displayColumnsAndStuff()

    Dim dEx As Worksheet
    Dim sumy As Worksheet
    
    Dim rng As Range
    
    Set dEx = Sheets("Data Export")
    Set sumy = Sheets("Summary")
    
    With sumy
        .Cells(1, 23).EntireColumn.value = dEx.Cells(1, 23).EntireColumn.value
        .Activate
        .Range(.Cells(1, 23), .Cells(.Cells(Rows.Count, 23).End(xlUp).Row, 23)).Select
        Selection.SpecialCells(xlCellTypeBlanks).Select
        Selection.Delete Shift:=xlUp
    End With
End Sub

This puts it into column W of the summary sheet as well (23)

 

[meppwc]

NeonRedSharpie...................this is great..............is there any way to make it display the results starting in cell N2 instead of column W ? If not, is there any way to make it display in column N?

 

[NeonRedSharpie]

Sub displayColumnsAndStuff()

    Dim dEx As Worksheet
    Dim sumy As Worksheet
    
    Dim rng As Range
    
    Set dEx = Sheets("Data Export")
    Set sumy = Sheets("Summary")
    
    With sumy
        .Cells(1, 14).EntireColumn.value = dEx.Cells(1, 23).EntireColumn.value
        .Activate
        .Range(.Cells(1, 14), .Cells(.Cells(Rows.Count, 14).End(xlUp).Row, 14)).Select
        Selection.SpecialCells(xlCellTypeBlanks).Select
        Selection.Delete Shift:=xlUp
    End With
End Sub

I'm assuming you want N2 because of a header. I moved it to N but because of the lazy way I coded it, it will paste it into N1. I can rewrite it if it's a big issue.

 

[meppwc]

No, this is fine.............thanks so much