Removing blank cells

  • Thread starter Thread starter Jambruins
  • Start date Start date
J

Jambruins

I have a column with data in it but there are blank cells in betwee
some of the data. Is there a way to remove these blank cells? I woul
prefer to do it with a formula because I have to do it a bunch of times
Thanks
 
Hi
not possible with a formula but maybe using a filter (Data - Filter)
and deleting these rows is a way for you
 
what about a formula in a separate column?

If my data is in column A could I use some formula in column B and hav
my data displayed in column B without spaces
 
Hi
in B1 enter:
=INDEX($A$1:$A$100,SMALL(IF($A$1:$A$100<>"",ROW($A$1:$A$100)),ROW(1:1))
)
and copy down
 
Maybe you could just sort your data?

Jambruins < said:
I have a column with data in it but there are blank cells in between
some of the data. Is there a way to remove these blank cells? I would
prefer to do it with a formula because I have to do it a bunch of times.
Thanks.
Click to expand...
 
Try this macro. I wrote it some time ago and it needs some error checking
etc but should work so long as you only highlight one column and want the
entire row hidden/deleted.
Gavin.Bird

Public Sub ZeroRows()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

mb = MsgBox("Hide/Delete Zero rows: Press 'Yes' to hide, 'No' to
delete", vbYesNoCancel)

If mb = vbYes Then
For Each c In Selection
c.Activate
If c.value = 0 Then c.EntireRow.Hidden = True
Next
ElseIf mb = vbNo Then
For Each c In Selection
c.Activate
If c.value = 0 Then c.EntireRow.Delete
Next
End If

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
 
Back
Top