Macro to sort data in spreadsheet but maintain empty rows between data.

copy data in sheet1 in sheet2 also for retrieval

try this macro test

Sub test()
Dim r As Range
Application.DisplayAlerts = False
Set r = Range(Range("A1"), Cells(Rows.Count, "A").End(xlUp))
r.Sort key1:=Range("A1"), header:=xlYes
Set r = Range(Range("A1"), Cells(Rows.Count, "A").End(xlUp))
r.Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(1), _
        Replace:=True, PageBreaks:=False
    Set r = Range(Range("B1"), Cells(Rows.Count, "B").End(xlUp))
   r.SpecialCells(xlCellTypeFormulas, 1).Clear
r.RemoveSubtotal
Columns("A:A").Delete
Application.DisplayAlerts = True
End Sub

Sub undo()
Worksheets("sheet1").Cells.Clear
Worksheets("sheet2").Cells.Copy Worksheets("sheet1").Range("A1")

End Sub

Hello venkat1926!

Thank you very much for your help. It is a great start for me.

Your code almost works, but I do not think that I was very clear and it does not do everything that I need it to.

After running your code on this:
ZZ091
ZZ091
ZZ091

XX189

ZZ092

XX190
XX190

XX191
XX191

I get:

ZZ091
XX189

XX190
XX190

XX191
XX191

ZZ091
ZZ091

ZZ092

I tried to figure out why the first row is not sorted correctly, but I could not figure it out.

This is very close to the result I was looking for. However, it does not keep the data in the other columns associated with the data in column A (I do not think I explained this very well, sorry).

I have several pieces of data in the columns that follow A that needs to move with the data in column A. So for example:

Column A Column C
ZZ091 data associated with ZZ091
ZZ091 data associated with ZZ091
ZZ091 data associated with ZZ091

XX189 data associated with XX189

ZZ092 data associated with ZZ092

XX190 data associated with XX190
XX190 data associated with XX190

XX191 data associated with XX191
XX191 data associated with XX191

should become:

Column A Column C
XX189 data associated with XX189

XX190 data associated with XX190
XX190 data associated with XX190

XX191 data associated with XX191
XX191 data associated with XX191

ZZ091 data associated with ZZ091
ZZ091 data associated with ZZ091
ZZ091 data associated with ZZ091

ZZ092 data associated with ZZ092

Is this possible to do with a macro? I am really struggling to figure it out.

Any help is much appreciated.

Thanks!