• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Transfer data using arrays with different results

YasserKhalil

YasserKhalil

Well-Known Member
Hello everyone
I have a code that is supoosed to transfer data when a criteria is met in sheet1 and it is transferred to sheet2
This is the code
Code: 
Sub TransferUsingArrays()
    Dim a, aOutput, iCol As Long, iRow As Long, iLooper As Long, x, r As Range
 
    Sheet2.Range("A2:L10000").ClearContents
 
    With Worksheets("Sheet1")
        a = .Range("A1").CurrentRegion
     
        For iCol = 2 To UBound(a, 2)
            If Application.WorksheetFunction.Sum(.Cells(2, iCol), .Cells(UBound(a, 1), iCol)) = 0 Then
                If r Is Nothing Then
                    Set r = .Columns(iCol)
                Else
                    Set r = Union(r, .Columns(iCol))
                End If
            End If
        Next iCol
     
        r.EntireColumn.Delete
        a = .Range("A1").CurrentRegion
    End With
 
    ReDim aOutput(1 To UBound(a) * UBound(a, 2), 1 To 12)
 
    For iCol = 2 To UBound(a, 2)
        For iRow = 2 To UBound(a)
            If a(iRow, iCol) > 0 Then
                iLooper = iLooper + 1
                aOutput(iLooper, 1) = iCol - 1
                aOutput(iLooper, 3) = a(1, iCol)
                aOutput(iLooper, 9) = a(iRow, 1)
                aOutput(iLooper, 12) = a(iRow, iCol)
            End If
        Next iRow
    Next iCol
 
    Sheet2.Cells(2, "A").Resize(iLooper, 12) = aOutput
End Sub


When executing the code in this case the number of results in sheet2 is 1708 rows ...

My problem is with this part (the second part of code not the first)


Code: 
        r.EntireColumn.Delete
        a = .Range("A1").CurrentRegion

If I commented out the following part and execute the code I got 1987 rows
Code: 
a = .Range("A1").CurrentRegion
Any idea why the results are different? and which of these results are true (1708 or 1987)
Thanks advanced for help
 

Attachments

  • Transfer Data.xlsm
    148.5 KB · Views: 7
Yes Mr. Marc and declared again to update the new range after the column deletion .. this is the point ..
If I declared for second time the results are different ..Why?
 
Hi ,

The problem is with using the CurrentRegion property ; this is a lazy programmer's shortcut , since it allows Excel to take the responsibility of defining the range of interest.

Excel does this by looking at a range which is bounded by blank rows and columns.

Put the following two lines of code and see whether the displayed addresses are the same :

Code: 
Debug.Print .Range("A1").CurrentRegion.Address
r.EntireColumn.Delete
Debug.Print .Range("A1").CurrentRegion.Address

a = .Range("A1").CurrentRegion
If both the addresses are identical , then the problem is elsewhere.

Narayan
 
Thanks for replies
In fact my problem is how to be sure of the two results >> Which of these two results are true??!
I know that after deletion the current region is changed ..
But why changed .. In code there is a criteria which is in this line
Code: 
If a(iRow, iCol) > 0 Then
This restrict the process of transfer ..
and the deletion of columns are concerned to the columns which have zeros in all of its cells .. so it is not supposed to deal with these columns ..
In fact I am still confused ..
 
Hi ,

Sorry , my mistake in not taking a deeper look at your problem.

See the attached file.

Narayan
 

Attachments

  • Transfer Data.xlsm
    298.6 KB · Views: 6
Thanks a lot Mr. Narayan
But It tooks time in executing this line
Code: 
.Columns(iCol).EntireColumn.Delete
as it deletes column by column ..

My code is working fine .. but it is weird to have different results ..if I commented out the line mentioned ..It is supposed theoretically that the results should be the same
 
Last edited:
Hi ,

The problem has nothing to do with the line of code.

The problem is that the earlier code for deleting the columns was deleting a few columns wrongly ; even columns which had non-zero data in them were being deleted ; thus the 1987 rows which comes from the 1987 non-zero cells originally present in the input data ( all 181 columns ) were being reduced to 1708 rows i.e. the 1987 non-zero cells were being reduced to 1708 non-zero cells. This was because quite a few non-zero cells were being eliminated because of the wrong columns being deleted.

I have rewritten the column deletion part to eliminate this problem.

Narayan
 
Do you mean that this part is wrong
Code: 
For iCol = 2 To UBound(a, 2)
            If Application.WorksheetFunction.Sum(.Cells(2, iCol), .Cells(UBound(a, 1), iCol)) = 0 Then
                If r Is Nothing Then
                    Set r = .Columns(iCol)
                Else
                    Set r = Union(r, .Columns(iCol))
                End If
            End If
        Next iCol
   
        r.EntireColumn.Delete
What is wrong exactly Mr. Narayan .. how to fix it instead of deleting column by column because the deletion in this case (column by column) is slower than storing the columns to a range variable then to delete the columns in one shot
 
Thanks a lot for this great help
it is better now
But I need to know is there a problem with that line
Code: 
If Application.WorksheetFunction.Sum(.Cells(2, iCol), .Cells(UBound(a, 1), iCol)) = 0 Then

As I noticed you replaced it with
Code: 
If Application.Evaluate("=SUM(" & Param & ")") = 0 Then
 
I think I found the problem now
I have edited this line
Code: 
If Application.WorksheetFunction.Sum(.Range(.Cells(2, iCol), .Cells(UBound(a, 1), iCol))) = 0 Then
And now everything is ok either I commented out the line or not
Code: 
a = .Range("A1").CurrentRegion

Thanks Mr. Narayan for your patience and for great help
 

As I wrote source was columns deletion …

You never had this issue with Columns instead of Range !
​
 
TBTO rule :​
Code: 
Sub Demo()
        Dim iCol&, L@, M@, Y@
    With Sheet1.Cells(1).CurrentRegion.Columns
        For iCol = 2 To .Count
               Y = Application.WorksheetFunction.Sum(.Range(.Cells(2, iCol), .Cells(.Rows.Count, iCol)))
               L = Application.Sum(.Cells(2, iCol).Resize(.Rows.Count))
               M = Application.Sum(.Item(iCol))
            Debug.Print iCol, M, L, Y
        Next
    End With
End Sub
 
You must log in or register to reply here.
Back
Top