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


--
xiang
------------------------------------------------------------------------
xiang's Profile: http://www.excelforum.com/member.php...o&userid=29489
View this thread: http://www.excelforum.com/showthread...hreadid=496567

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

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" wrote in
message ...

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

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

Thanks for your response, Tom

I may not describe my question clearly. What I'd like to do is
1) putting worksheet information into array
2)check one or more conditions for a column in array and DELETE
those rows in which the conditions are met

for example: A 1 2 3 4
B 5 6 7 8
C 9 3 2 4
A 3 4 5 6

result is like : B 5 6 7 8
C 9 3 2 4
delete those rows with "A" in the column 1
3) write the results in array back to the worksheet to avoid deleting
rows in worksheet.


--
xiang
------------------------------------------------------------------------
xiang's Profile: http://www.excelforum.com/member.php...o&userid=29489
View this thread: http://www.excelforum.com/showthread...hreadid=496567

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

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.

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

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

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" wrote in
message ...

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.

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

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

Hi, Tom
thanks for your suggestion.
But I'm afraid that Transpose approach won't work when the elements o
array
exceed 5160. BTW, I'm using excel 2000, and I don't know what wil
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

--
xian
-----------------------------------------------------------------------
xiang's Profile: http://www.excelforum.com/member.php...fo&userid=2948
View this thread: http://www.excelforum.com/showthread.php?threadid=49656

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

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" wrote in
message ...

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