Using an array to store row numbers

[cr731]

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:

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:

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.

 

[BiocideJ]

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

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]

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

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]

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

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)

 

[cr731]

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

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]

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.

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