VBA Filter with criteria and offset

[Flakie]

Hello,

I am filtering column A (the only column in the sheet) for all rows that begins with an @ sign.
The delete all rows that do not start with an @ sign
I have some code which does this OK.

However I also want the filter to return the row directly above the one found with the @ sign.
And keep this row when the rest are deleted.

I am guessing I need to use offset in some way?
There will never be two rows next to each other that start with an @ sign so no additional sanity checking is needed.

Eventually I would like the row above, that does not contain the @ sign, to be moved to the column (B) next to its related one containing the @ sign. Hope this makes sense.
Guessing I have to use transpose here?

This si what i have so far:

    Set ws = ActiveWorkbook.Sheets("Testing")
    lastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
    Set rng = ws.Range("A1:A" & lastRow)
    With rng
        .AutoFilter Field:=1, Criteria1:="<>*@*"
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
    ws.AutoFilterMode = False

Is what I need available in Excel?
If so, is anyone able to help?

Many thanks,
Paul

 

[Flakie]

Sorry to bother you again. How would I amend the code so that either:
1. The 3 rows above the @ sign to be moved to the column (B) (preferred)
2. The 3 rows above the @ sign to be moved to the columns (B), (C) and (D) (I can figure out how to join them together in one column myself if not possible otherwise).

I can see it is the code:

<code>
Ar(i).Offset(, 1).Value = Ar(i).Offset(-1).Value
</code>

that selects the first row above the @ row (specifically the offset -1 value) but have been unable to get it to move all 3 rows.

The complicated thing may be they might be empty rows?

Many thanks.
Paul.

 

[Flakie]

Of course just after I posted I had a light bulb moment:

Ar(i).Offset(, 1).Value = Ar(i).Offset(-1).Value & Ar(i).Offset(-2).Value & Ar(i).Offset(-3).Value

is this the correct way?

 

[Fluff]

That's one way, another is

Sub Flakie()
   Dim Ar As Areas
   Dim i As Long, Lr As Long
   
   With Sheets("Testing")
      If .AutoFilterMode Then .AutoFilterMode = False
      Lr = .Range("A" & Rows.Count).End(xlUp).Row
      .Range("A1:A" & Lr).AutoFilter 1, "*@*"
      Set Ar = .AutoFilter.Range.SpecialCells(xlVisible).Areas
      For i = 2 To Ar.Count
         Ar(i).Offset(, 1).Value = Join(Application.Transpose(Ar(i).Offset(-3).Resize(3).Value), ", ")
      Next i
      .Range("A1:A" & Lr).AutoFilter 1, "<>*@*"
      .AutoFilter.Range.Offset(1).SpecialCells(xlVisible).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub

If the first @ is in rows 2 or 3 this will fail

 

[Flakie]

Brilliant. Many thanks again. Will give it a try later