Creating 2 dimensional array from larger 2 dimensional array

Question

I can't get around the below problem and any help would be greatly appreciated

I have a 2 dimensional array that looks like the below;

I want to create a new array (PFArr) from this, but without the rows where deleted = "yes". I could make this new array the same size as the original one, just leaving blank rows where i have removed data, but this array will be used in numerous loops down the code so i want to make it as compact as possible.

With the below code i have tried to get the number of elements where deleted = 0 ( which i want to keep)... using a loop.

Then redim the new array to this size.

Then it should assign the relevant elements from the old array to the new array with the second loop. However it is not assigning the elements into the array as expected, it seems to be looping through fine, just not adding the new elements in. Any ideas?

Sub AddSelectDataFromBigArrayToSmallOne()

Dim PFAllArr As Variant
Dim PFArr As Variant
Dim c1, i1, c2, i2 As Long

PFAllArr = Sheets("PF File Simple").Range("A2").CurrentRegion.Value

'get number of elements i want to transfer to new array
c1 = 1
For i1 = LBound(PFAllArr) To UBound(PFAllArr)
    If PFAllArr(i1, 2) = 0 Then
    c1 = c1 + 1
    End If
Next i1

'Make new array this size
ReDim PFArr(LBound(PFAllArr) To c1, 1 To 4)

'Assign elements from old array nto new one
c2 = 1
For i2 = LBound(PFAllArr) To UBound(PFAllArr)
    If PFAllArr(i2, 2) = 0 Then
    PFArr(c2, 3) = PFAllArr(i2, 3)
    PFArr(c2, 4) = PFAllArr(i2, 4)
    c2 = c2 + 1
    End If
    Debug.Print c2, PFArr(c2, 3), PFArr(c2, 4)
Next i2

End Sub

You have 3 solutions below. You should accept one of them to give credit to people that donate their time to help others :) — urdearboy
– urdearboy, Commented Dec 1, 2018 at 17:37
Ah i see. I did not know this. I thought the number of up votes decided that. Thanks again — Stephen
– Stephen, Commented Dec 3, 2018 at 0:42
Ah i see. I did not know this. I thought the number of up votes decided that. Thanks again — Stephen
– Stephen, Commented Dec 3, 2018 at 0:43

tigeravatar · Accepted Answer · 2018-11-30 18:34:58Z

4

Perhaps something like this?

Sub tgr()

    Dim aTemp As Variant
    Dim aData As Variant
    Dim iyTemp As Long
    Dim iyData As Long
    Dim ix As Long

    With ActiveWorkbook.Sheets("PF File Simple").Range("A2").CurrentRegion
        aTemp = .Value
        ReDim aData(1 To WorksheetFunction.CountIf(.Resize(, 1).Offset(, 1), 0), 1 To .Columns.Count)
    End With

    For iyTemp = 1 To UBound(aTemp, 1)
        If aTemp(iyTemp, 2) = 0 Then
            iyData = iyData + 1
            For ix = 1 To UBound(aTemp, 2)
                aData(iyData, ix) = aTemp(iyTemp, ix)
            Next ix
        End If
    Next iyTemp

    'aData is now populated with only values where the second column is 0

End Sub

answered Nov 30, 2018 at 18:34

tigeravatar

26.7k5 gold badges32 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stephen Over a year ago

That looks a lot slicker than my code tigeravatar thanks! I got mine working with with urdearboys suggestion above. I can see that your way is more efficient though, will use that in the future

urdearboy · Accepted Answer · 2018-11-30 19:23:34Z

3

Two things

You will need to change c1 to start at 0 instead of 1. If you declare your variable type right, you can just not set the initial value. Long type variable will default to 0 unless stated otherwise
Your variable block Dim c1, i1, c2, i2 As Long is not working how you may think it is. You are actually declaring i2 as Long and the rest of your variables are being pushed into Variant type variable. This part is less important, but chives well on this topic: you can also just use Dim PFAllArr, PFArr and these will default to Variant

Thus, you can start your code like so (removed your c1 set, and updated lines to declare variables as intended):

Sub AddSelectDataFromBigArrayToSmallOne()

Dim PFAllArr, PFArr
Dim c1 as Long, i1 as Long, c2 as Long, i2 As Long

PFAllArr = Sheets("PF File Simple").Range("A2").CurrentRegion.Value

For i1 = LBound(PFAllArr) To UBound(PFAllArr)
    If PFAllArr(i1, 2) = 0 Then
    c1 = c1 + 1
    End If
Next i1

IMO, @tigeravatar's solution looks cleaner. Regardless of which code you end up using, it is always nice to see why yours didn't work

edited Nov 30, 2018 at 19:23

answered Nov 30, 2018 at 19:12

urdearboy

14.6k6 gold badges35 silver badges64 bronze badges

3 Comments

tigeravatar Over a year ago

Great explanation for the issue OP encountered and how to correct it, +1

T.M. Over a year ago

Fine explanation +1, maybe interested in my approach,too.

Stephen Over a year ago

Yes that is definitely true regarding finding out why your own code isn't working. I learned a lot from fixing that and it would have been wrecking my head for weeks if i didn't fix it :)

T.M. · Accepted Answer · 2018-11-30 19:35:49Z

Using Application.Index

You can use the advanced filtering possibilities of the Index function:

Main procedure

Sub DelRows()
  Dim PFAllArr, PFArr
' [1a] create 2-dim data field array (1-based)
  PFAllArr = ThisWorkbook.Worksheets("PF File Simple").Range("A2").CurrentRegion.Value2
' [1b] filter out rows to be deleted
  PFArr = Application.Transpose(Application.Index(PFAllArr, getAr(PFAllArr, 2), Evaluate("row(1:" & UBound(PFAllArr, 2) & ")")))
End Sub

Helper function getAr()

Function getAr(v, ByVal colNo&) As Variant()
' Purpose: collect row numbers not to be deleted (criteria <> "YES" in 2nd column)
' Note:    called by above procedure DelRows
  Dim ar, i&, n&
  ReDim ar(0 To UBound(v) - 1)
  For i = 1 To UBound(v)
      If UCase$(v(i, colNo)) <> "YES" Then
         ar(n) = i: n = n + 1
      End If
  Next i
  ReDim Preserve ar(0 To n - 1): getAr = ar
End Function

Collectives™ on Stack Overflow

Creating 2 dimensional array from larger 2 dimensional array

3 Answers 3

1 Comment

Two things

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Two things

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related