If a cell is not null return the value in column &

Question

I have a list of products listed on one worksheet where you can select how many of each you want, but not every product will have a value. In a separate worksheet I want it to only pull over the product that have a amount selected. Please Advice.

First Worksheet:

A        B

a        3

c       45

d   

e   

f       10

Desired Second Worksheet:

A       B

b       3

c      45

f      10

Answer 1

You could use a VBA subroutine to achieve this, if you/your end user is allowed macros. In the example below I'm assuming you haven't renamed the worksheets and they're called "Sheet1" and "Sheet2" respectively - if you've renamed them you'll have to change them in the code to match.

1) Show the developer toolbar:

[http://www.traineetrader.com/excel-quick-tips-howto-enable-the-developer-toolbar-in-excel-20102011/][1]

2) On the developer toolbar click insert, then add a button (doesn't matter where)

3) right click the button and 'Assign Macro' then click 'New'

4) this code should do it:

Sub Button1_Click()

Dim row As Integer

row = 1
newrow = 1

Do Until Worksheets("Sheet1").Cells(row, 1).Value = ""

    If Worksheets("Sheet1").Cells(row, 2).Value <> "" Then

    Worksheets("Sheet2").Cells(newrow, 1).Value = Worksheets("Sheet1").Cells(row, 1).Value
    Worksheets("Sheet2").Cells(newrow, 2).Value = Worksheets("Sheet1").Cells(row, 2).Value

    newrow = newrow + 1

    End If

row = row + 1

Loop

End Sub

Any questions - let me know!

Answer 2

You can get what you want with a simple VBA subroutine:

Sub notNull()
    Dim count As Integer
    count = Application.WorksheetFunction.CountA(Range("A:A"))
    Dim i As Integer
    i = 1
    Dim rowCount As Integer
    rowCount = 1
    Do While i <= count
        If (Range("B" & i) <> "") Then
            Worksheets("Sheet2").Range("A" & rowCount) = Range("A" & i)
            Worksheets("Sheet2").Range("B" & rowCount) = Range("B" & i)
            rowCount = rowCount + 1
        End If
        i = i + 1
    Loop
End Sub

What it does is it goes through all of the rows with data in column A, checks if the associated value in column B is equal to "", and if it isn't, it copies over both values onto another sheet. Hope this helps!