Hi,
I was hoping to find a good way to do the following.

I have a table that consists of blocks of data 5 columns wide and a
variable number of rows for each block.

There are no spaces in between, just stacked data. The one
commonality in each block is column A values, such as the value
123456Lab. In this 123456Lab block, all rows will have column A value
of 123456Lab, for lets say 8 rows. This block could be located
anywhere vertically in the data table.

I need to search for the variable, in this case 123456Lab, and select
the entire range (this block is 8 rows by 5 columns).

The variable will be a listbox entry; so basicall a cell vaue entry.

Any ideas on the best way to find and select the above range?

That range will then be copy/pasted someplace else (I think I can do
that part).

Also, I will be going back and selecting that same row range later and
deleting the rows, so they are gone. If any ideas there as well?

Thanks Again!
Steve

hi Steve,

Sub test1()
test = "123456Lab" 'adapt for your listbox
mn = Range("$A1:$A65535").Find(test, LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext).Row

mx = Range("$A1:$A65535").Find(test, LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Row

Set TheRange = Range(Cells(mn, 5), Cells(mx, 5))
MsgBox TheRange.Address
End Sub


--
isabelle



Le 2012-01-16 17:37, SS a écrit :
Hi,
I was hoping to find a good way to do the following.

I have a table that consists of blocks of data 5 columns wide and a
variable number of rows for each block.

There are no spaces in between, just stacked data. The one
commonality in each block is column A values, such as the value
123456Lab. In this 123456Lab block, all rows will have column A value
of 123456Lab, for lets say 8 rows. This block could be located
anywhere vertically in the data table.

I need to search for the variable, in this case 123456Lab, and select
the entire range (this block is 8 rows by 5 columns).

The variable will be a listbox entry; so basicall a cell vaue entry.

Any ideas on the best way to find and select the above range?

That range will then be copy/pasted someplace else (I think I can do
that part).

Also, I will be going back and selecting that same row range later and
deleting the rows, so they are gone. If any ideas there as well?

Thanks Again!
Steve