Need help copy range >0

[Lukums]

G'day,

I'm trying to copy this table and paste into another sheet.

Let's say this example is Column A and Column B (sheet1)

The paste into another sheet is fine and I'm across that but I'm having trouble selecting the range where zeros are NOT present.

3500	12
4520	6
2100	92
0	0
0	0

<tbody>
</tbody>

So the solution would be:

3500	12
4520	6
2100	92

<tbody>
</tbody>

Thanks all!

 

[sheetspread]

Filter the zeroes, select with F5(Goto)-Special-Visible cells only, then copy & paste.

 

[Lukums]

Filter the zeroes, select with F5(Goto)-Special-Visible cells only, then copy & paste.

Thought about that!

But! If I use .autofilter cell (whatever) = >0
and then autofilter = false

It unhides all my hidden rows so basically only 31 rows are visible up to column BB

 

[Michael M]

Hi Lukums
Does this do what you want...

Sub MM1() 'no header row
 With ActiveSheet.UsedRange
            .AutoFilter
            .AutoFilter field:=1, Criteria1:=">0"
            .Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet2").Range("A1")
            .AutoFilter
    End With
End Sub

 

[Lukums]

Hi Lukums
Does this do what you want...

Sub MM1() 'no header row
 With ActiveSheet.UsedRange
            .AutoFilter
            .AutoFilter field:=1, Criteria1:=">0"
            .Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet2").Range("A1")
            .AutoFilter
    End With
End Sub

You're probably right as rain (as always) however I think! If I do, do it this way I will need to find the last entry in column A and paste below.
But I'll recheck this iteration and get back to you asap! As always thank you!

 

[Michael M]

Maybe this then

Sub MM1() 'no header row
 Dim LR As Long
 LR = Sheets("Sheet2").Cells.SpecialCells(xlCellTypeLastCell).Row + 1
 With ActiveSheet.UsedRange
            .AutoFilter
            .AutoFilter field:=1, Criteria1:=">0"
            .Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet2").Range("A" & LR)
            .AutoFilter
    End With
End Sub