Getting Rid of Blank Spaces in Data

mooseapalooza

New Member
Joined
Jul 23, 2007
Messages
2
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.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
ASAP Utilities has a feature to allow removal of empty rows.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Deletes a row if totally empty:....


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

End Sub
 
Upvote 0

Forum statistics

Threads
1,217,757
Messages
6,138,429
Members
450,137
Latest member
HANHAN

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top