automatically select next group of cells

**iturnrocks** · 11-21-2007, 06:32 PM

I have a workbook that contains part numbers and their components listed like below. There is always one empty row between the part number and the components, and then 3 empty rows between the last component and the next part number. The number of components varies from sometimes only 1 up to 25. Currently, I select a part number and its components like B1:C5 and then I have a macro that copies it to another workbook. What I would like to do is somehow get the macro to automatically select the next set down, in this case, B9:C14, so I could copy every set over to its proper place with only one click of the button.

If anyone knows how to do this, I appreciate the help.

**mikerickson** · 11-21-2007, 07:20 PM

If you have one data group selected. This will select the next data group.

Dim baseRange As Range

With Selection
    Set baseRange = Cells(.Row + .Rows.Count + 3, 2)
End With
Set baseRange = Range(baseRange.Offset(0, 1), baseRange.Offset(2, 0).End(xlDown))

baseRange.Select

**iturnrocks** · 11-21-2007, 07:34 PM

Originally Posted by mikerickson

If you have one data group selected. This will select the next data group.

I tried it and it selected the next set along with every set after that.

My example only showed 2 sets, but there are hundreds.

I need it to only select the next set, then my macro will run again, then select the next set and so on.

**mikerickson** · 11-21-2007, 08:04 PM

The overall plan should be

Do
    Call yourMacro
    
    With Selection
        Set baseRange = Cells(.Row + .Rows.Count + 3, 2)
    End With
    Set baseRange = Range(baseRange.Offset(0, 1), baseRange.Offset(2, 0).End(xlDown))
    baseRange.Select

Loop Until Done

If you post your code, it would be easier to integrate the "next group" part. I'm going to be leaving town for the weekend. I'll look at this again when I get back to see if you need any more help.

**iturnrocks** · 11-21-2007, 08:12 PM

Thanks. I had no luck on my workbook, so I opened a new blank one, just copied in my example from the post and it worked. I still have to figure out why it wont work on my sheet. If I cant figure it out, perhaps I will post my workbook later. Im just kinda embarrassed to show off my amateur excel skills.