Stepping backward through an array

  • Thread starter Thread starter quartz
  • Start date Start date
Q

quartz

Please help if possible:

I am trying to step backward through an array and my code
looks like the following:

dim lngX as long
For lngX = UBound(arrRows) To 1 Step -1
Rows(arrRows(lngX)).EntireRow.Delete
Next lngX

It seems to me this should work. Does anyone see any
errors?
 
I have just tried, loading arrows as follows

Dim arrRows(3)

arrRows(0) = 3
arrRows(1) = 5
arrRows(2) = 7
arrRows(3) = 8

and it worked as I expected.

What are you seeing?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
quartz,

For IngX = UBound(arrRows) To 1 Step -1

Should be changed to:

For IngX = UBound(arrRows) To 0 Step -1

or:

For IngX = UBound(arrRows) to LBound(arrRows) Step -1

Remember that arrays are defined starting from 0. (i.e. Dim MyArray(
To 10) as String
 
array sdon't "always" start base 0 or base 1

eg
Dim x(-5 To -2, 5 To 20) As String

Your recommendation to use both UBOUND() and LBOUND() is
the only one that would work in these cases.


The issue with the original code is really more to do
with the values in the array, not the direction through
which it is looped.
Deleting rows should be done from the highest row number
to the lowest to avoid "losing" our initial row indexs
and deleleting the wrong row.
If we want to delete rows 3 and 5. deleting row 3 makes
waht was row 5 now row 4. our code would deleet row 5,
thus corrupting our data. Hence, delete 5 them 3.

The key then to the success of the loop s that the array
must have the data sorted into ascending order.
The loop could as easily be done normally if the data is
sorted descending.

Patrick Molloy
Microsoft Excel MVP
 
Pyball, In VBA arrays can be define with lowerbounds other than zero
using the syntax you supplied.
 
Is your array multi-dimensional, perhaps?

If you defined your array something like this:

Dim arrRows As Variant
arrRows = Sheet1.Range("A6:A15").Value

then you will have a 2D array, thus you'd need to change the your code
to this:

Dim lngX As Long
For lngX = UBound(arrRows, 1) To 1 Step -1
Rows(arrRows(lngX, 1)).EntireRow.Delete
Next lngX

Aside: consider qualifying what Rows refers to e.g. Sheet1,
Application.ActiveSheet, etc.
 
Back
Top