Copy cell range if cell has data paste offset

[Capt_Antihero]

Hello,

I have a spreadsheet with data that sometimes every 20th row is misaligned. The sheet will be of varying row lengths depending on the data so I have nothing set. What I need is a macro in VBA that will find the first cell in A1 of Sheet1 with data and if there is data in that cell (lets say the first cell it finds is A20) then to select 9 cells including A20 (A20:J20) and copy and paste the data as an offset 2 cells to the right (it will end up being column C)

Once this is done I would need the macro to keep looping through column A until it finds every cell with data as above and completes the copy and paste offset so I need it to loop until there is no data in column A. Can any excel expert help me with this seemingly simple macro?

 

[L. Howard]

If I understand your query, this may do that.

Copy to the sheet module.

Regards,
Howard

Option Explicit

Sub Cut_Off()

Dim c As Range
Dim Lrow As Long
Dim aRng As Range
Lrow = Cells(Rows.Count, "A").End(xlUp).Row
Set aRng = Range("A1:A" & Lrow)

For Each c In aRng
  If c <> "" Then
    c.Resize(1, 10).Cut c.Offset(, 2)
  End If
Next

End Sub

 

[Capt_Antihero]

Howard, that is perfect, you nailed it thanx a million!!!

 

[L. Howard]

Okay, great. Thanks for the feed back.

Regards,
Howard