Condensing a Data Set

[london735]

Hello!
I want to ask if there is a way to simply extract non-empty rows from a data set consisting of many rows filled with zeroes and some which are filled with the non-zero values. By “simply” I mean without using any macros. Any help or suggestions are highly appreciated.

PS: I have attached the workbook with example of the sheet with such data set (CLEAN tab shows the desired result of this operation):

[EducatedFool]

Try to use the following macro:

Sub CopyRows()
    Application.ScreenUpdating = False
    Dim shSource As Worksheet: Set shSource = ActiveSheet
    Dim shDest As Worksheet: Set shDest = ThisWorkbook.Worksheets.Add
    shDest.Name = "New sheet " & Format(Now, "HH-NN-SS"): shDest.Tab.Color = vbGreen

    Dim cell As Range
    For Each cell In shSource.UsedRange.Columns(1).Cells
        If cell.Value <> 0 And Trim(cell.Value) <> "" Then
            cell.EntireRow.Copy shDest.Range("a" & Rows.Count).End(xlUp).Offset(1).EntireRow
        End If
    Next
    shDest.Rows(1).Delete
End Sub

Press the green button placed on RAW sheet in attached file:

[honeybeer]

Solution without macro.
Try to use "AutoFilter" (in main menu "Data"->"Filter"->"AutoFilter")
1) Insert row with headers for each data column above a data range

2) Select all data columns on "RAW" sheet

3) Use "AutoFilter" and condition "not equal 0"

No macros, no troubles

[london735]

thank you for your nice replies but I cannot use wither a macro or the traditional autofilter methods,....I need this to be done with formulas alone...is there a way to do this?

[roplius]

Сообщение от london735

thank you for your nice replies but I cannot use wither a macro or the traditional autofilter methods,....I need this to be done with formulas alone...is there a way to do this?

with formulas

[london735]

Hello Roplius!
Sorry for the delayed reply. Can you please tell me what the "MyRow" expression stands for?
Thanks!!!