Remove blank rows when creating new sheet with formula

[Jezza25]

Hi All,


Firstly I understand I could filter and remove blank rows but in this instance for automation I actually need a formula


My data looks as follows, what I require is a formula so that it on a new sheet only shows the numbers if the cell has a number it will show the number, if the cell as shown in A4 and A5 are blank the following number will actually be what is in A6. Final result I am seeking is shown further below
Existing Sheet
1234
3232
4242
<Blank>
<Blank>
6435
2342
<Blank>
4535
<Blank>


New Sheet
1234
3232
4242
6435
2342
4535

 

[MARK858]

Formula in New Sheet A1 must be array entered with Cttl-Shift + Enter not just Enter and dragged down. Do you enter the curly brackets manually.

Excel Workbook
	A
1	1234
2	3232
3	4242
4
5
6	6435
7	2342
8
9	4535
Existing Sheet

Excel Workbook
	A
1	1234
2	3232
3	4242
4	6435
5	2342
6	4535
7
8
New Sheet

 

[My Aswer Is This]

Try this:
Will copy from Sheet(1) to Sheet(2)

Sub Auto_Filter()
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

    With Worksheets("Sheet1").Range("A1:A" & Lastrow)
        
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:="<> "
        .SpecialCells(xlCellTypeVisible).Copy Worksheets("Sheet2").Range("A1")
    End With
    
ActiveSheet.AutoFilterMode = False
End Sub

 

[Jezza25]

Thanks you Firtstly what you supplied worked so thanks. I was meant to include 1 more thing but I could not edit. The reason for the formula is because if I go back to my original quastion the data extraction is showing orders and when someone order 2 things the order id is blank but the quantity still appears I also need a way of adding the quantity. SO for4 order id because there was 3 items I need the quantity to actualy show a total on the subsequant sheet of 74 ( 4 + 36 +_ 34)

Existing Sheet
ID Quantity
1234 54
3232 45
4242 4
<blank style="color: rgb(87, 65, 35); line-height: 15.99px;"> 36
<blank> 34
6435 3
2342 19
<blank> 23
4535 5</blank></blank></blank>


Next t

Formula in New Sheet A1 must be array entered with Cttl-Shift + Enter not just Enter and dragged down. Do you enter the curly brackets manually.

Existing Sheet

*	A
*
*
*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]1234[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]3232[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]4242[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]6435[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]2342[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]4535[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


New Sheet

*	A
*
*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]1234[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]3232[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]4242[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]6435[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]2342[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]4535[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula A1 {=IFERROR(INDEX('Existing Sheet'!$A$1:$A$9, SMALL(IF(ISBLANK('Existing Sheet'!$A$1:$A$9), "", ROW('Existing Sheet'!$A$1:$A$9)-MIN(ROW('Existing Sheet'!$A$1:$A$9))+1), ROW(A1))),"")} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Jezza25]

Just on the above all the quanitties appear in the same column so it actually has the 36 under the 4

 

[MARK858]

On the Existing Sheet select the column with the order numbers - Ctrl + G - click special - put a checkmark in the blanks checkbox - click OK - press the = button then the up arrow button - Ctrl + Enter.

The gaps should now be filled in. Select the column and copy/paste as values.

On the new sheet

Array entered formula in A2 (Not A1) and dragged down. Sumif in B2 and dragged down.

Excel Workbook
	A	B
1	1234	54
2	3232	45
3	4242	4
4	4242	36
5	4242	34
6	6435	3
7	2342	19
8	2342	23
9	4535	5
Existing Sheet

Excel Workbook
	A	B
2	1234	54
3	3232	45
4	4242	74
5	6435	3
6	2342	42
7	4535	5
New Sheet