Selecting blocks of data with VBA end.xldown and end.xltoright

[mudshark]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]XX_ID_A

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Site
[/TD]
[TD]Name
[/TD]
[TD]Manager
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Fred
[/TD]
[TD]9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Mary
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Jack
[/TD]
[TD]9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Jill
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]XX_ID_B
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]System
[/TD]
[TD]Plan
[/TD]
[TD]Fname
[/TD]
[TD]Lname
[/TD]
[TD]Man
[/TD]
[TD]Note
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]TAD
[/TD]
[TD]John
[/TD]
[TD]Smith
[/TD]
[TD]6
[/TD]
[TD]Service
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]TAD
[/TD]
[TD]Jerry
[/TD]
[TD]Jones
[/TD]
[TD]6
[/TD]
[TD]HR
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]TAC
[/TD]
[TD]Alan
[/TD]
[TD]Berry
[/TD]
[TD]7
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]TAB
[/TD]
[TD]Mary
[/TD]
[TD]Dawson
[/TD]
[TD][/TD]
[TD]Sales
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hi
I have some VBA that selects blocks of data from one main sheet based on an identifier in column A and
copies these to separate sheets
But the data can be different sized ranges and non-contiguous data in the right-most columns.
(Left-most column is always filled.)
So using the following code doesn't always select all the data.
After selecting the cell below the XX_ID identifier i then try to select the whole block.

Set ACTrng = Range(Selection, Selection.End(xlDown).End(xlToRight))

Similar issue if I change the order (xlToRight then xlDown)

a simple example would look something like above.

How do i approach this..
thanks in advance...

 

[mudshark]

I may have stumbled on the answer to my own question ??? ... select in 2 steps....

    Set TopRowrng = Range(Selection, Selection.End(xlToRight))
    Set ACTrng = Range(TopRowrng, Selection.End(xlDown).End(xlToRight))

Is that a robust method ?

 

[DanteAmor]

Try this

Sub test()
  Dim rng As Range
  For Each rng In Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
    rng.EntireRow.Copy
  Next
End Sub

With the above you can copy the block.
Where do you want to paste it, on a new sheet or does the sheet already exist?