Trying to get row numbers using Range object to Copy range to array

I am using the Range object to copy a range of rows from a worksheet into an array. I need to be able to edit various cells in the rows from the array and use the row number to copy the changed rows back to the worksheet. Is there any way to do this with my approach or must I revert back to reading row by row into the array so that I can query the row number by active cell? I can't find how to do my approach, but can't believe there isn't an easier approach than reading row by row to determine each row.

Please define

a range of rows

Where do they start? Where do they finish? Is that variable? How do you determine start and finish? Are you storing the entire row or just the first how ever many columns? Cell references would be useful.

If you know where the range starts and ends and you don't decrease or increase the number of rows, it should be easy enough to put them back where you found them.

Bear in mind that you are looking at your spreadsheet and you know what you want to do with it. We're not and don't.

Regards, TMS

I have no data and no code so, just as a quick experiment, I made this routine to populate a range, store it in an array and print out the content.

Please Login or Register  to view this content.

So, from the debug output, you can see the lower bounds and upper bounds of the array. And you can determine the content of each element in the array.

I guess, in broad terms, that is what you are doing. Presumably, column B has a key field? And, maybe you delete the key field if you want to delete a row? And Redim Preserve the array to add new rows to the end. And you can amend the elements within the array.

Whatever, for what it's worth, the row number maps to the array index plus one (+1). So, you know where stuff is. Similarly, the column number.

If you consider that you have an input array that you work on ... however you do that ... when you're finished, you could loop through that input array and move the entries to an output array if the key field isn't blank. Then just clear the input RANGE and write the output ARRAY back to the worksheet.

If you track how many "rows" you delete and how many you add, you can calculate the dimensions of the output array AND the range that you will write to.

Anyway, my thoughts on the subject. Hope it helps. I see no reason why you need to operate on a row by row basis.

Regards, TMS

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.