array values to worksheet

[mcranmoss]

I have checked that the array is populated but the following code is failing to tranfer the array values to column A of the 'Category' sheet.

Any advice welcome.

For Each x In tmpArray
 
 Cells(Sheets("Category").Range("A2").End(xlDown).Row, 1).Value = x
 
Next x

thanks
mark

 

[Jon von der Heyden]

Is the entire intention for this routine to pull out unique product codes? Or will there be more to it?

 

[Sektor]

Sub DumpArray()

    Dim tmpArray As Variant
    Dim ProdCode As Variant
    
    'Dump the range into a 2D array
    LastRow = Sheets("Matrix Codes").Range("A2").End(xlDown).Row
    lastCol = Sheets("Matrix Codes").Range("A2").End(xlToRight).Column
    
    With Sheets("Matrix Codes")
      tmpArray = .Range(.Cells(2, 1), .Cells(LastRow, lastCol))
    End With

    'Convert 2D to 1D
    ReDim Preserve tmpArray(1 To UBound(tmpArray, 1), 1)
    
    Sheets("Category").Range("A2").Resize(UBound(tmpArray, 1)) = tmpArray

End Sub

 

[Jon von der Heyden]

If it's just unique products that you are after:

Sub DedupeProducts()
    Dim LastRow As Long
      
    LastRow = Sheets("Matrix Codes").Range("A2").End(xlDown).Row
    
    With Sheets("Matrix Codes").Range("A2:A" & LastRow)
        Call .AdvancedFilter(Action:=xlFilterCopy, CopyToRange:=Sheets("Category").Range("A2"), Unique:=True)
    End With
End Sub

 

[mcranmoss]

Your code works great. There appears to be an issue with my attempt to initially read in a 2D range. Only the first column of the worksheet is read into the array.

LastRow = Sheets("Matrix Codes").Range("A2").End(xlDown).Row
[B]LastCol = Sheets("Matrix Codes").Range("A2").End(xlToRight).Column
[/B]catLastCell = Sheets("Category").Range("A2").End(xlDown).Row
 
With Sheets("Matrix Codes")
  tmpArray = .Range(.Cells(2, 1), .Cells(LastRow, [B]LastCol[/B])).Value
End With

In term of the number of rows. There should never be more than 30 or so.

thanks a great deal for your help

mark

 

[mcranmoss]

The problem with autofilter is the fact that I will be working with a 2D range. At least that's the theory.


mark

 

[Jon von der Heyden]

Sektors code also demonstrates another method. One can only resize the last dimension of a multi-diemnsional array, and this can be used to your advantage.

However, if you are just needing a list of unique product codes then see my last advanced filter suggestion.

 

[Jon von der Heyden]

The problem with autofilter is the fact that I will be working with a 2D range. At least that's the theory.


mark

I'm losing the plot a bit, sorry... Why do you need to handle a 2d range if you are after a list of unique product codes?

 

[mcranmoss]

you are quite right. For no good reason I thought your autofilter suggestion applied to the Matrix Codes sheet, which of course makes no sense. Applying an autofilter to the destination column at the end of the procedure is a perfect solution.

I'm struggling work out why my code is only picking up the first column from the Matrix Codes sheet.

thanks again. sorry it's been a bit of a messy thread.

mark

 

[Sektor]

There's a method of Range called RemoveDuplicates. Just dump array to sheet and call this method on dumped cells.

 

[mcranmoss]

thanks Sektor, I'll give it a go.

Looping through my 1D array with MsgBox demonstrates that values from both column A and B have been included in the array. When this array is dumped into the first column of the category sheet, only the values from column A of the source worksheet are copied. This doesn't seem to be the expected behaviour of a 1D array.

anyway. Thanks again.

mark