Select variable range in the middle of worksheet down to the next blank row

**rookie1ja** · 06-05-2009, 04:38 AM

Hi guys, I am sure this is a piece of cake for you, however, I could not find solution for this task.

My sheet looks as follows:

lots of data
lots of data
blank row
lots of data (including cell named "real cost")
blank row
...
'and here comes the table of variable length that I want to copy
'cell in column H is named "real cost" - so it's the 2nd time from the beginning that cell has such text
'I want to select area from the H cell "real cost" to cell in the same row in A column and down to the next blank row (without the blank row)
....
lots of data
blank row
lots of data

So basically, I want to select the variable range somewhere in the middle of the sheet from where the 2nd instance of cell named "real cost" is, down to the next blank row (select the area without the blank row), so that I could copy it to another sheet.

Thanks a lot.

**royUK** · 06-05-2009, 05:49 AM

Taake a look at the CurrentRegion or UsedRange in the VBA help or attach a workbook.

**rookie1ja** · 06-05-2009, 06:40 AM

I have attached the xls file (I have deleted the real data).

For this case, I would select/copy range A449:H505 (the yellow area in my attachment). But this area will change for another case.

The rule is:
1. find the 2nd instance of "*Real* Cost (BMC)" in cells from the beginning (it should be in column H)
2. select area from there to cell in column A (on the same row)
3. continue with selecting area down until you encounter blank row (this number of filled rows is variable)
4. select the variable range (without the blank row) and then copy it

Thanks.

**rookie1ja** · 06-06-2009, 05:27 AM

Bump no response resolving my issue.

Pls could you have a look at my post above - 4 points with what I want to achieve and the xls file is attached in the post as well.

Thanks.

**royUK** · 06-08-2009, 04:03 AM

This code will do what you describe

    Const sFind As String = "*Real* Cost (BMC)"
    Dim rng    As Range
    Dim rSearch As Range
    Dim cl     As Range
    Dim StartRw As Long
    Dim i      As Integer

    With ActiveSheet
        Set rSearch = .Columns(8)
        With rSearch
            Set cl = .Find(sFind, LookIn:=xlValues)
            If Not cl Is Nothing Then
                Do Until i = 2
                    i = i + 1
                    Set cl = .FindNext(cl)
                Loop
            End If
        End With
        StartRw = cl.Row
        Set rng = .Range(.Cells(StartRw, 1), .Cells(StartRw, 8).End(xlDown))
        rng.Select
    End With

You don't actually need to select the range to perform an action on it such as copying.

**rookie1ja** · 06-08-2009, 04:36 PM

royUK, your code is GREAT :-)

I have just amended it to go to the second instance of "*Real* Cost (BMC)" and select area from that 2nd instance.

   Const sFind As String = "*Real* Cost (BMC)"
   Dim rng    As Range
   Dim rSearch As Range
   Dim cl     As Range
   Dim StartRw As Long
   Dim i      As Integer

   With ActiveSheet
       Set rSearch = .Columns(8)
       With rSearch
           Set cl = .Find(sFind, LookIn:=xlValues)
           Set cl = .FindNext(cl)
       End With
       StartRw = cl.Row
       Set rng = .Range(.Cells(StartRw, 1), .Cells(StartRw, 8).End(xlDown))
       rng.Select
   End With

I like your solution very much - simple and beautiful.

Thanks a lot.