Variable Range, selecting/copying

[SS]

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

 

[isabelle]

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 :