Getting Rid of Blank Spaces in Data

[mooseapalooza]

This is a pretty simple issue but I cant figure out a straightforward way do to it (I only started using excel at my new job a few weeks ago). I have a column with 100 data points in it. Each data point is seperated by 24 blank cells. Hence, cell A1 has some value, cells A2-A25 are empty, cell A26 has some value,...and so on for a few thousand cells.

I want to get rid of the blank spaces. I want cell A1 to contain its value. cell A2 to contain cell A26 value, cell A3 to contain cell A51 value, and so on.

Please help. I am banging my head against my cubicle in frustration.

 

[MOB]

ASAP Utilities has a feature to allow removal of empty rows.

 

[michal]

If you have just one column (or many columns with similar gaps between the values) you can simply delete the empty rows (2-25 etc). Mark them on the left margin and after right click hit delete. This will move up everything below.

 

[mooseapalooza]

The problem is that I would have to perform this operation hundreds of times. There has to be a faster way to get rid of all the empty spaces I think.

 

[MOB]

ASAP Utilities!

 

[michal]

axe break

make a macro for this function, and eventually macro of macros.

 

[ADAMC]

to delete blank rows:

Sub DeleteEmptyRows(DeleteRange As Range)
' Deletes all empty rows in DeleteRange
' Example: DeleteEmptyRows Selection
' Example: DeleteEmptyRows Range("A1:D100")
Dim rCount As Long, r As Long
If DeleteRange Is Nothing Then Exit Sub
If DeleteRange.Areas.Count > 1 Then Exit Sub
With DeleteRange
rCount = .Rows.Count
For r = rCount To 1 Step -1
If Application.CountA(.Rows(r)) = 0 Then
.Rows(r).EntireRow.Delete
End If
Next r
End With
End Sub

Change range accordingly

 

[ADAMC]

Deletes a row if totally empty:....


Sub DeleteRowOnCell()
On Error Resume Next
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange

End Sub