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

  • Thread starter Thread starter xiang
  • Start date Start date
X

xiang

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
 
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.
 
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.
 
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.
 
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
 
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!
Click to expand...

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