Excel formula only bring over row in other worksheet if cell in column A is not blank

Question

I have two worksheets in one Excel workbook, and I only want to take the lines that have data in the cell (from worksheet1 into worksheet2) if Column A has data in it. My formula in worksheet 2 is =IF('Raw Data'!A2<>"", 'Raw Data'!A2,), but I actually don't want it to bring in the row at all if there is no data as shown in Rows 3 and 5. Right now it is bringing the whole row in:

sample data1

In

sample data2

you see that it is still bringing the row into worksheet 2 if there is no data. Any ideas how to only bring in the rows with the data?

Sub DataInCell()

Dim rw As Long
rw = 2

' Select initial sheet to copy from
Sheets("Raw Data").Select


' Find the last row of data - xlUp will check from the bottom of the spreadsheet up.
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
' For loop through each row
For x = 2 To FinalRow

       If Cells(x, 1).Value <> 0 Then
        Range("A" & x & ":C" & x).Copy
       Sheets("Sheet1").Select
        NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1 'Continue incrementing through the rows.
        Cells(NextRow, 1).Select ' Find the next row.
        ActiveSheet.Cells(NextRow, "A").PasteSpecial xlPasteAll ' Paste information.
        Sheets("Raw Data").Select 'Reselect sheet to copy from. Probably uneccessary.
        End If

Next x
End Sub

Are you open to VBA solution? Other solutions would be to just filter/copy paste visible cells. Or pivot table with a filter on non-blanks in your target column. using formulas for this seems pretty inefficient — urdearboy
I am open to VBA solutions! I just thought there might be a simple formula solution that I was missing. Any ideas on how to do it in VBA? — SurrHolbs
What columns do you want to take from your master sheet onto the new sheet? The entire row? Please update your question to show the origin column and destination column if it is not the entire row — urdearboy
I got it to bring in the cell from column A with the code I posted (edited) above, but I need to bring in cells from A-C. Do you know how to do that referencing the variables? — SurrHolbs

urdearboy urdearboy · Accepted Answer · 2018-12-13T17:52:08

After you update the sheet names on the 3rd and 4th line, you will see that the code carries over the entire row. You can modify using Range(Cells, Cells) if you want partial ranges.

Option Explicit

Sub Non_Blanks()

Dim ms As Worksheet: Set ms = ThisWorkbook.Sheets("Sheet1") '<-- Master Sheet
Dim ns As Worksheet: Set ns = ThisWorkbook.Sheets("Sheet2") '<-- New Sheet

Dim i As Long, MoveMe As Range, LR As Long

For i = 2 To ms.Range("B" & ms.Rows.Count).End(xlUp).Row
    If ms.Range("A" & i) = "*" Then
        If Not MoveMe Is Nothing Then
            Set MoveMe = Union(MoveMe, ms.Range("A" & i))
        Else
            Set MoveMe = ms.Range("A" & i)
        End If
    End If
Next i

If Not MoveMe Is Nothing Then
    LR = ns.Range("A" & ns.Rows.Count).End(xlUp).Offset(1).Row
    MoveMe.EntireRow.Copy
    ns.Range("A" & LR).PasteSpecial xlPasteValuesAndNumberFormats
End If

End Sub

Excel formula only bring over row in other worksheet if cell in column A is not blank

1 Answers