Get row indices where...

Hi All,

I'm writing some VBA in which I need to get the row indices of all the rows in a spreadsheet whose column A contains a particular value, how might I do this?

Also is there a way I can assign all the values of cells in a row to an array? Such that the first element of the array would contain the value of column A, the second element the value of column B and so on.

All help will be much appreciated.

Yes I guess you could call it the row number, though it is also frequently refered to as the row index in VBA documentation.

As for the code: Activecell.row, I think I may need a little bit more to achieve the desired result I specified in my first post.

Can anyone else help?

To assign all cells in a row to an array, you could do something like this:

Please Login or Register  to view this content.

Having said that ... I would almost ask ... what's the point? A row is already very much like an array.

I wrote "almost" because I can think of one very good reason to transfer cell data into an array ... that would be to sort the data without sorting in Excel. Having said that, however, sorting in Excel is usually a lot faster than doing a bubble sort of an array. But, if you have a more advanced array sorting technique, it is probably just as fast.

If this is not what you were looking for, my apologies. Ask again.

Cheers,

To be honest that part of my question wasn't the most important bit, I only wanted to assign the row to an array so that it would be easier to reference later on in the code i.e. writting MyArray(1) rather than worksheet("Sheet1").Cells(x,x). I was hoping there was a simple one or two line way of doing so. But I do appreciate the response.

Just a quick question on that and the previous response, do VBA coders tend to use ActiveSheet and ActiveCell more than specifically referencing the sheet or cell with worksheet("Blah").Cells(x,x)?

Just to repeat my original main question (so it doesn't get lost):

Is there a way to get the row indices (or row numbers if you like) of all the rows in a worksheet whose column A contains a particular value?

Cheers Carim,

That'll work. Not quite sure why I didn't go down that route myself, I focused on the idea there'd be a built in function to do it.

Cheers