Remove blank rows by VBA

[santoshloka]

i have some random values in 2 colums those have some gaps between rows in "sheet1"
i want to remove gaps between the values and past those values in "sheet 2"
please find the attachment below
https://drive.google.com/open?id=0B8-usD7SI_4aRzM2bXJYV1VlQVU

 

[hiker95]

i have some random values in 2 colums those have some gaps between rows in "sheet1"
i want to remove gaps between the values and past those values in "sheet 2"
please find the attachment below
https://drive.google.com/open?id=0B8-usD7SI_4aRzM2bXJYV1VlQVU

santoshloka,

So that we can get it right on the first try, can you supply another workbook with what the results (manually formatted by you) on Sheet2, should look like?

 

[hiker95]

santoshloka,

Here is a macro solution for you to consider.

With your raw data in worksheet Sheet1, Range("A1:B15"), the results will be written to Sheet2, Range("A1:B8").

Sub ReorganizeData()
' hiker95, 10/09/2017, ME1025635
Application.ScreenUpdating = False
Sheets("Sheet1").UsedRange.Copy Sheets("Sheet2").Range("A1")
With Sheets("Sheet2")
  .UsedRange.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
  .UsedRange.Columns.AutoFit
End With
Application.ScreenUpdating = True
End Sub