help! how to delete useless rows in 2 dim array by VBA

I just would like to fulfill autofilter function in a 2-dim array, and then delete
useless rows in that array. Is that doable or not?

as you know, deleting rows is so painful in worksheet even though autofilter
is faster.

any idea or example would be much appreciated.

thanks

Putting information in an array won't provide any gain if you are still
deleting rows in the worksheet.

You can filter so that the rows to delete are visible then

Dim rng as Range
set rng = activesheet.AutofilterRange.Columns(1)
set rng = rng.offset(1,0).Resize(rng.rows.count-1)
On Error Resume Next
set rng1 = rng.specialcells(xlvisible)
On Error goto 0
if not rng1 is nothing then rng1.EntireRow.delete

--
Regards,
Tom Ogilvy

"xiang" <[email protected]> wrote in
message news:[email protected]...
>
> I just would like to fulfill autofilter function in a 2-dim array, and
> then delete
> useless rows in that array. Is that doable or not?
>
> as you know, deleting rows is so painful in worksheet even though
> autofilter
> is faster.
>
> any idea or example would be much appreciated.
>
> thanks
>
>
> --
> xiang
> ------------------------------------------------------------------------
> xiang's Profile:
http://www.excelforum.com/member.php...o&userid=29489
> View this thread: http://www.excelforum.com/showthread...hreadid=496567
>

hi, Tom
here is your code to remove blank entries from array
It works perfect in one-dim array. How could I make it work for 2-dim array.
that is something I want.

Re: removing blank entries from an array

--------------------------------------------------------------------------------

j = lbound(ar) - 1
for i = lbound(ar) to ubound(ar)
if ar(i) <> "" then
j = j + 1
ar(j) = ar(i)
end if
Next
Redim Preserve ar(lbound(ar) to j)

--
Regards,
Tom Ogilvy

The problem with a 2D array is that your can not redim preserve on the first
dimension (rows). You would basically have to copy to a second array as you
go, or another approach would be to use application.Transpose to reverse the
row and column order.

Sub AdjustArray()
Dim ar As Variant, v As Variant
Dim i As Long, j As Long, k As Long
ReDim ar(1 To 20, 1 To 5)
' Build a test array with some empty elements in the first column
For i = 1 To 20
If Rnd() < 0.5 Then
For j = 1 To 5
ar(i, j) = Int(Rnd() * 100 + 1)
Next
End If
Next i
Range("A1:E20").Value = ar
v = Application.Transpose(ar)

j = LBound(v, 2) - 1
For i = LBound(v, 2) To UBound(v, 2)
If Not IsEmpty(v(1, i)) Then
j = j + 1
For k = LBound(v, 1) To UBound(v, 1)
v(k, j) = v(k, i)
Next
End If
Next
ReDim Preserve v(LBound(v, 1) To UBound(v, 1), LBound(v, 2) To j)
ar = Application.Transpose(v)
Range("G1").Resize(UBound(ar, 1), UBound(ar, 2)).Value = ar
Erase v
End Sub

For simplicity, some of the code assumes a lower bound of 1 in each
dimension. Since your array will come from a range, this should not be a
problem.

--
Regards,
Tom Ogilvy




"xiang" <[email protected]> wrote in
message news:[email protected]...
>
> hi, Tom
> here is your code to remove blank entries from array
> It works perfect in one-dim array. How could I make it work for 2-dim
> array.
> that is something I want.
>
> > Re: removing blank entries from an array
> >
>
> --------------------------------------------------------------------------
------
> >
> > j = lbound(ar) - 1
> > for i = lbound(ar) to ubound(ar)
> > if ar(i) <> "" then
> > j = j + 1
> > ar(j) = ar(i)
> > end if
> > Next
> > Redim Preserve ar(lbound(ar) to j)
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
>
>
> --
> xiang
> ------------------------------------------------------------------------
> xiang's Profile:
http://www.excelforum.com/member.php...o&userid=29489
> View this thread: http://www.excelforum.com/showthread...hreadid=496567
>

Hi, Tom
thanks for your suggestion.
But I'm afraid that Transpose approach won't work when the elements of array
exceed 5160. BTW, I'm using excel 2000, and I don't know what will happen in execl 2003.

Could you offer another approach?

one more thing,
For i = 1 To 20
If Rnd() < 0.5 Then
For j = 1 To 5
ar(i, j) = Int(Rnd() * 100 + 1)
Next
End If
Next i

I don't understand above lines.
If you can explain a little bit, that would be prefect!

In xl2003, it isn't a problem. Here is an alternate approach that should
work in xl97/2000

Sub AdjustArray11()
Dim ar As Variant, v As Variant
Dim i As Long, j As Long, k As Long
ReDim ar(1 To 6000, 1 To 5)
' Build a test array with some empty elements in the first column
For i = 1 To 6000
If Rnd() < 0.5 Then
For j = 1 To 5
ar(i, j) = Int(Rnd() * 100 + 1)
Next
End If
Next i
Range("A1:E6000").Value = ar
j = 0
For i = LBound(ar, 1) To UBound(ar, 1)
If Not IsEmpty(ar(i, 1)) Then
j = j + 1
End If
Next
ReDim v(1 To j, 1 To 5)
j = 0
For i = LBound(ar, 1) To UBound(ar, 1)
If Not IsEmpty(ar(i, 1)) Then
j = j + 1
For k = 1 To 5
v(j, k) = ar(i, k)
Next
End If
Next
ar = v
Range("G1").Resize(UBound(ar, 1), UBound(ar, 2)).Value = ar
Erase v
End Sub


> one more thing,
> For i = 1 To 20
> If Rnd() < 0.5 Then
> For j = 1 To 5
> ar(i, j) = Int(Rnd() * 100 + 1)
> Next
> End If
> Next i
>
> I don't understand above lines.
> If you can explain a little bit, that would be prefect!

Those lines just generate a test array that has blank rows in it. That is
just for demonstration purposes.

--
Regards,
Tom Ogilvy



"xiang" <[email protected]> wrote in
message news:[email protected]...
>
> Hi, Tom
> thanks for your suggestion.
> But I'm afraid that Transpose approach won't work when the elements of
> array
> exceed 5160. BTW, I'm using excel 2000, and I don't know what will
> happen in execl 2003.
>
> Could you offer another approach?
>
> one more thing,
> For i = 1 To 20
> If Rnd() < 0.5 Then
> For j = 1 To 5
> ar(i, j) = Int(Rnd() * 100 + 1)
> Next
> End If
> Next i
>
> I don't understand above lines.
> If you can explain a little bit, that would be prefect!
>
>
> --
> xiang
> ------------------------------------------------------------------------
> xiang's Profile:
http://www.excelforum.com/member.php...o&userid=29489
> View this thread: http://www.excelforum.com/showthread...hreadid=496567
>

I was able to utilize 2 arrays to solve this problem. The first array ingests all the raw data, then clears certain rows based on a value in a cell. Once that is done, I feed that array into a new array skipping the "Blank" rows.



Sub TestDeleteArrayRows()

'Pull all data into first array
Dim TestArr() As Variant
Dim i As Long, j As Integer, k As Long, z As Long
Sheets("DSA").Activate
TestArr = Range(Cells(2, 1), Cells(100, 5))
k = 0
For i = LBound(TestArr) To UBound(TestArr)
'Clear array row based on predetermined factor
If TestArr(i, 4) = "10" Then
For j = 1 To 5
TestArr(i, j) = ""
Next j
Else:
'Count non-empty rows in array
k = k + 1
End If
Next i

'New array without empty rows
Dim ResArr()
ReDim ResArr(1 To k, 1 To 5)
z = 1
For i = 1 To UBound(TestArr)
'If the row from the first array is not empty, then bring it into the new array
If TestArr(i, 1) <> "" Then
For j = 1 To 5
ResArr(z, j) = TestArr(i, j)
Next j
z = z + 1
End If
Next i


'*****Output for testing*****
Sheets("Test").Activate
Range(Cells(2, 1), Cells(k + 1, 5)) = ResArr
'*************************

End sub

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

No need an array just using Excel basics ‼

With an additional column to mark rows to keep or to delete aka 0 / 1 or False / True via a formula for example.
Sort in ascending way the range on this additional colum, so the rows to delete are at the end of range.
Clear - and not Delete ! - the block of rows to "delete" at once ! Then Clear additional column, that's it !
See samples in threads of this forum …