Can’t remove blank rows

[gz3s36]

Is there a way to populate sheet2 from sheet1 while removing blank rows?

[johnmpl]

Hi, gz3s36!

One option with formulae. Blessings!

[AlKey]

On your Expected Results sheet enter formula in A1 and drag formula across and down

***Array formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around your formula.
If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

=IFERROR(INDEX(PROBLEM!A:A,SMALL(IF(PROBLEM!A$1:A$200<>"",ROW(A$1:A$200)),ROWS(A$1:A1))),"")

For Excel (2010, 2013, 2016) use this regular formula

=IFERROR(INDEX(PROBLEM!A:A,AGGREGATE(15,6,ROW(A$1:A$200)/(PROBLEM!A$1:A$200<>""),ROWS(A$1:A1))),"")

[gz3s36]

On your Expected Results sheet enter formula in A1 and drag formula across and down

***Array formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around your formula.
If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

=IFERROR(INDEX(PROBLEM!A:A,SMALL(IF(PROBLEM!A$1:A$200<>"",ROW(A$1:A$200)),ROWS(A$1:A1))),"")

For Excel (2010, 2013, 2016) use this regular formula

=IFERROR(INDEX(PROBLEM!A:A,AGGREGATE(15,6,ROW(A$1:A$200)/(PROBLEM!A$1:A$200<>""),ROWS(A$1:A1))),"")

Is there a way for it to not return 0 for blank cells?

[AlKey]

Yes there is a way
For array formula

=IFERROR(INDEX(PROBLEM!A:A,SMALL(IF((PROBLEM!A$1:A$200<>"")*(PROBLEM!A$1:A$200<>0),ROW(A$1:A$200)),ROWS(A$1:A1))),"")

For non-array

=IFERROR(INDEX(PROBLEM!A:A,AGGREGATE(15,6,ROW(A$1:A$200)/((PROBLEM!A$1:A$200<>"")*(PROBLEM!A$1:A$200<>0)),ROWS(A$1:A1))),"")

[gz3s36]

Just aren't having any luck.

[AlKey]

Just aren't having any luck.

Luck? This is nothing to do with luck.
Here is an attached file with two tables. On the left with array formula on the right is non-array.

[alansidman]

Here is a VBA solution

Option Explicit

Sub foo()
    Dim s1 As Worksheet, s2 As Worksheet
    Dim i As Long, lr As Long, lr2 As Long
    Set s1 = Sheets("PROBLEM")
    Set s2 = Sheets("EXPECTED RESULT")
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 1 To lr
        lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
        If s1.Range("A" & i) <> "" Then
            s1.Range("A" & i & ":C" & i).Copy s2.Range("A" & lr2 + 1)
        End If
    Next i
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "complete"
End Sub

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

[gz3s36]

I can't make it work. Either solution.

[alansidman]

Your issue is that you don't have one consistent column to be evaluated. Suggest you reformat your spreadsheet to have at least one column to evaluate. It would have been helpful from the start if you had presented a sample workbook that was representative of your actual needs. Based upon your original presentation, both solutions would work. Help us to help you in the future by presenting representative samples. We cannot read your mind or see your screen.

[alansidman]

This code tests for any blank rows. If the entire row is blank, then it copies the data in columns A:S to Sheet2. You have a link to an outside worksheet. This interferes with the code. You may wish to break the link before running the code.

Option Explicit

Sub foo()
    Dim s1 As Worksheet, s2 As Worksheet
    Dim i As Long, lr As Long, lr2 As Long
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 1 To lr
        lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
        If WorksheetFunction.CountA(Range("A" & i & ":S" & i)) <> 0 Then
            s1.Range("A" & i & ":S" & i).Copy s2.Range("A" & lr2 + 1)
        End If
    Next i
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "complete"
End Sub