Using an array to store row numbers

cr731

Well-known Member
Joined
Sep 17, 2010
Messages
611
I have a macro which does two parts:

1. Runs through each row of my table and checks if the row is a match to some entries by the user. It then counts and displays the number of matches:

Code:
For i = 1 to MyTable.Rows.Count

'If row is a match
Counter = Counter + 1

Next i

Msgbox "x lines match your criteria"

2. The next step is that for each line that is a match, the macro actually changes a value in that table row of a specified column. Currently it repeats much of number 1:


Code:
For i = 1 to MyTable.Rows.Count

'If row is a match
Change value of ListColumn

Next i

Msgbox "x lines match your criteria"

The thing is, I need these two to be able to be run differently so that the user can see how many records are impacted without going to the next step and changing the values. I feel there must be a more efficient way as I'm checking every row twice. Can an Array store each row number of a match and then use that in #2 - and would that be more efficient? My table could have up to 60,000-70,000 rows, so I also wasn't sure how many values an array can handle.
 
Last edited:

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
You can set an array to the values of an entire range and vice-versa. If performance in your current execution is slow then this may be a good alternative.

Something like this air-code
Code:
Dim myArr as Variant
myArr = myRange.Value
'change some values in myArr
myRange.Value = myArr
As far as max array size, I believe the limit has more to do with the size of RAM then any preset ceiling. I may be wrong on this, but I have used some awfully large arrays and I don't ever recall getting a size limit error.
 

cr731

Well-known Member
Joined
Sep 17, 2010
Messages
611
You can set an array to the values of an entire range and vice-versa. If performance in your current execution is slow then this may be a good alternative.

Something like this air-code
Code:
Dim myArr as Variant
myArr = myRange.Value
'change some values in myArr
myRange.Value = myArr
As far as max array size, I believe the limit has more to do with the size of RAM then any preset ceiling. I may be wrong on this, but I have used some awfully large arrays and I don't ever recall getting a size limit error.

Thanks but I don't quite see how this would work - I need the macro to run in two steps.

1. Check each row and see if it's a match; count the number of matches.

2. The user then sees that there were x number of matches; if they are okay with it, they click 'Next' and the macro updates just those rows identified in #1 with new values.
 

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
I suppose I didn't explain myself very well did I? Sorry about that.
My concept is to load the range values into an array and then you can loop through the array and count/change all at once since changes in the array aren't committed anywhere.
If the user decides to accept the changes then the entire contents of the array are dumped back into the initial range. This works because the array is just the original values of the range with the exception of the values you changed.

Putting it all somewhat more together it would look like this
Code:
Dim myArr as Variant
myArr = myRange.Value
For i = LBound(myArr) to UBound(myArr)
If RowIsAMatch Then
     Counter = Counter + 1
     'change some values in myArr
     'example myArr(i,2) = newValue ...where 2 is the column of the row to change
Next i
UserSaysUpdate = MsgBox (Counter & "rows to Update.  Continue", vbYesNo, "User Prompt")
If UserSaysUpdate = vbYes Then
     myRange.Value = myArr
End If

This method is exceptionally fast because there is essentially one read (loading the array), one loop (through that array), and one write (copying the array to the range if the user requests)

In your initial method there are 2*N reads (number of rows for reading and changing) and X writes (number of rows changed)
 
Last edited:

cr731

Well-known Member
Joined
Sep 17, 2010
Messages
611
I suppose I didn't explain myself very well did I? Sorry about that.
My concept is to load the range values into an array and then you can loop through the array and count/change all at once since changes in the array aren't committed anywhere.
If the user decides to accept the changes then the entire contents of the array are dumped back into the initial range. This works because the array is just the original values of the range with the exception of the values you changed.

Putting it all somewhat more together it would look like this
Code:
Dim myArr as Variant
myArr = myRange.Value
For i = LBound(myArr) to UBound(myArr)
If RowIsAMatch Then
     Counter = Counter + 1
     'change some values in myArr
     'example myArr(i,2) = newValue ...where 2 is the column of the row to change
Next i
UserSaysUpdate = MsgBox (Counter & "rows to Update.  Continue", vbYesNo, "User Prompt")
If UserSaysUpdate = vbYes Then
     myRange.Value = myArr
End If

This method is exceptionally fast because there is essentially one read (loading the array), one loop (through that array), and one write (copying the array to the range if the user requests)

In your initial method there are 2*N reads (number of rows for reading and changing) and X writes (number of rows changed)

Where you have myArr(i,2) = ... can the 2 be a column header name? The reason is that there is a potential that the column ordering could change; though the header should not. Right now I do this by the following

MyListObject.ListColumns("Column Name").DataBodyRange(i).Value = newValue
 

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
You wouldn't be able to do this directly in the array arguments because a number is required, however, you could set a variable using something along the lines of a Range.Find to be the column #.

i.e.
Code:
myChangeCol = Range("1:1").Find("Column Name").Column
myArr(i,myChangeCol) = ...
 

Watch MrExcel Video

Forum statistics

Threads
1,108,910
Messages
5,525,585
Members
409,651
Latest member
Quasar Hunter

This Week's Hot Topics

Top