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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

MOB

Well-known Member
Joined
Oct 18, 2005
Messages
1,048
Office Version
  1. 365
Platform
  1. Windows
ASAP Utilities has a feature to allow removal of empty rows.
 

michal

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

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

Well-known Member
Joined
Oct 18, 2005
Messages
1,048
Office Version
  1. 365
Platform
  1. Windows
ASAP Utilities!
 

michal

New Member
Joined
Jul 23, 2007
Messages
3
axe break

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

ADAMC

Well-known Member
Joined
Mar 20, 2007
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Mar 20, 2007
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
Deletes a row if totally empty:....


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

End Sub
 

Forum statistics

Threads
1,181,658
Messages
5,931,272
Members
436,786
Latest member
Deniel

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
Top