How to use Redim

[idnoidno]

Will the use of instr, how to edit brr and find out(filter) all the data ?

19349

[mdmackillop]

I'm guessing a bit as to your code intention.
You can only redim the second dimension of an array. In the attached, a 23 x 4 area is used to populate a 4 x 23 array. This is re-dimmed to 4 x 6 or whatever and transposed to give a 6 x 4 result.
Note that this is not the method I would use to carry out this task.

[idnoidno]

Hey, why do you re-dimmed to 4 x 6 array and transposed to 6 x 4 result? Use directly 6 x 4 is wrong?This is what I still do not understand the transposed method.

[mdmackillop]

You cannot directly redim 23 x 4 to 6 x 4. You need to populate the array in a manner which allows the redimming. You therefor put values from a 23 x 4 range into a maximum 4 x 23 array, resize it and then transpose.

[idnoidno]

Why do you use "ReDim brr(LC, LR)" instead "ReDim brr(LR, LC)"?

[mdmackillop]

So I can later ReDim Preserve brr(LC, j)

[idnoidno]

LR = Cells(Rows.Count, 1).End(xlUp).Row
LC = Cells(1, Columns.Count).End(xlToLeft).Column

ReDim brr(LC, LR)
Set rg = Range(Cells(1, 1), Cells(LR, LC))
arr = rg
For i = StartRow To UBound(arr)
If InStr(arr(i, Col), Txt) > 0 Then
j = j + 1
For k = 1 To LC
brr(k, j) = arr(i, k)
Next k
End If
Next i
ReDim Preserve brr(LC, j)

Set wst = Sheets.Add(after:=ActiveSheet)
wst.Range("a1").Resize(j, LC) = Application.Transpose(brr)


I may not know well about red code(redim & Transpose), can you explain further?

I wish I could have a very clear expression about my question.

[idnoidno]

Sub matchcheck2()
Dim rg As Object
Dim wst As Worksheet
Dim i As Long, j As Long, k As Long
Dim LR As Long, LC As Long
Dim StartRow As Long
Dim Col As Long, Txt As String
Dim arr
Dim brr
Col = 2
Txt = "Trial 03"
StartRow = 2


LR = Cells(Rows.Count, 1).End(xlUp).Row
LC = Cells(1, Columns.Count).End(xlToLeft).Column


ReDim brr(LR, LC)
Set rg = Range(Cells(1, 1), Cells(LR, LC))
arr = rg
For i = StartRow To UBound(arr)
If InStr(arr(i, Col), Txt) > 0 Then
j = j + 1
For k = 1 To LC
brr(j, k) = arr(i, k)
Next k
End If
Next i


Set wst = Sheets.Add(after:=ActiveSheet)
wst.Range("a1").Resize(j, LC) = brr
End Sub


If I don't use redim, I could get almost the same result.
Do I have to use REDIM?




Test03
Trial 03
Other 03
More 52


Test07
Trial 03
Other 04
More 56


Test10
Trial 03
Other 03
More 59


Test14
Trial 03
Other 03
More 63


Test18
Trial 03
Other 04
More 67


Test21
Trial 03
Other 03
More 70





Test03
Trial 03
Other 03
More 52


Test07
Trial 03
Other 04
More 56


Test10
Trial 03
Other 03
More 59


Test14
Trial 03
Other 03
More 63


Test18
Trial 03
Other 04
More 67


Test21
Trial 03
Other 03
More 70

[mdmackillop]

There are a lot of resources that can be found using Google e.g. https://excelmacromastery.com/excel-vba-array/

[idnoidno]

Option Explicit
Sub filterCopy()
Dim lastrow As Integer
Dim lastcolumn As Integer
Dim rg As Range
Dim i As Integer, j As Integer, k As Integer
Dim arr As Variant
Dim brr()
Set rg = [a1].CurrentRegion
arr = rg
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
lastcolumn = Cells(1, Columns.Count).End(xlToLeft).Column
ReDim brr(1 To lastrow, 1 To lastcolumn - 1)
k = 1
For i = 2 To lastrow
If arr(i, 7) = "Y" Then
For j = 1 To lastcolumn - 1
brr(k, j) = arr(i, j)
Next j
k = k + 1
End If
Next i
Worksheets("sheet2").Select
Range("a1").Resize(UBound(brr, 1), UBound(brr, 2)) = brr
Set rg = Nothing
End Sub


Although this CODE can be carried out, but the scope of redim is declared relatively large, I think this is not the right way, how can I change it?

[mdmackillop]

I'm giving up on Redim. Just use Filter+Copy

Option Explicit
Sub filterCopy()
Dim rng As Range
Set rng = Range("A1").CurrentRegion
With rng
.AutoFilter Field:=7, Criteria1:="Y"
.Offset(1).Resize(rng.Rows.Count - 1, rng.Columns.Count - 1).Copy Sheets(2).Range("A1")
.AutoFilter
End With
End Sub