Removing zeros from range

[jlp87]

Hi guys

For the last two days I've been trying to write a sub that'll remove all zeros from a given range. The range could look like this:

A
1: 0
2: 2
3: 0
4: 7
5: 0

I have written the following piece of code to remove the zeros and re-print the range without the zeros somewhere else in the sheet:

Sub PrintArray(Data As Variant, Cl As Range)    
    Cl.Resize(UBound(Data, 1)) = Data
    
End Sub


Public Sub Test()
    
    Dim MyArray() As Variant
    ReDim MyArray(1 To Range("N273").Value) 'Right now "N273"'s value is 35
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim i As Integer
          
    Set wb = Application.Workbooks(1)
    Set ws = wb.Sheets(1)
    Set rng = ws.Range("J1275", "J2612")
    
    i = 0


    For Each cell In rng.Cells
        
            If cell > 0 Then
                i = i + 1
                MyArray(i) = cell
            End If
        
    Next cell
    
    PrintArray MyArray, Range("L323")


End Sub

No matter how I tweak the code, I only manage to get the first or last value from the range re-printed. Right now the code re-prints the first value in the range (8) 35 times. I have tried Googling and going through numerous forums, but I haven't found an answer yet. I'm quite new to VBA and programming in general, therefore I hope one of you might spot my error

Thanks.


Best regards
jlp87

 

[general_payne]

Hmmm, your code seems overly convoluted for just checking for 0's. I don't think you need the array. I'd use something like the following instead:

Sub Exclude0()
Dim c As Range
Dim i As Integer
i = 1
'Assuming Sheet 1, range(A1:A500) contains the numbers that you want to exclude the 0's from
    For Each c In Sheets("Sheet1").Range("A1:A500")
        If Not c.Value = 0 Then
            'The results will be placed in Sheet 2, starting at cell A1 and going down
            Sheets("Sheet2").Range("A" & i).Value = c.Value
            i = i + 1
        End If
    Next
End Sub

I haven't checked your code too much, as I'm not the best at arrays, but let me know if you specifically need your code working and I'll have more of a look. Otherwise, the above is a solution.

Thanks

 

[tlowry]

Welcome to MrExcel

try:

Sub PrintArray(Data As Variant, Cl As Range)
    Cl.Resize(UBound(Data, 1)) = Application.WorksheetFunction.Transpose(Data)
End Sub

if you want the array approach.

 

[jlp87]

Hmmm, your code seems overly convoluted for just checking for 0's. I don't think you need the array. I'd use something like the following instead:

I haven't checked your code too much, as I'm not the best at arrays, but let me know if you specifically need your code working and I'll have more of a look. Otherwise, the above is a solution.

Thanks

Thanks for your reply. Your example work flawlessly on my sheet. I've tweaked it a bit to work on the active sheet instead of sheet 1 and 2. I have 10 similar sheets I want it to work on.

Sub Exclude0()Dim c As Range
Dim i As Integer
i = 322


    For Each c In ActiveSheet.Range("J1275:J2612")
        If Not c.Value = 0 Then
            ActiveSheet.Range("L" & i).Value = c.Value
            i = i + 1
        End If
    Next
           
End Sub

If I want to print c.Value transposed like: "L322", "K322" etc. instead of downwards like: "L322", "L323" etc., is there an easy solution to that?

Thanks

 

[general_payne]

If I want to print c.Value transposed like: "L322", "K322" etc. instead of downwards like: "L322", "L323" etc., is there an easy solution to that?

Yes, change it to:

Sub Exclude0()
Dim c As Range
Dim i As Integer
'12 = Col L
i = 12


    For Each c In ActiveSheet.Range("J1275:J2612")
        If Not c.Value = 0 Then
            ActiveSheet.Cells(322, i).Value = c.Value
            i = i + 1
        End If
    Next
           
End Sub

Cheers

 

[jlp87]

Thanks, tlowry. It works perfect. I'll stick to that solution

 

[jlp87]

Yes, change it to:

Sub Exclude0()
Dim c As Range
Dim i As Integer
'12 = Col L
i = 12


    For Each c In ActiveSheet.Range("J1275:J2612")
        If Not c.Value = 0 Then
            ActiveSheet.Cells(322, i).Value = c.Value
            i = i + 1
        End If
    Next
           
End Sub

Cheers

You guys are guru's. Thanks a lot. I can't believe I didn't post my question here way before

Problem solved. The thread may be closed.