Deleting Blank Cells or Zero Value Cells ?

  • Thread starter Thread starter Daniel Rascoe
  • Start date Start date
D

Daniel Rascoe

I have spreadsheet with about 9000 rows with the following data in cells A1
to A10
7,0,0,-4,0,0,0,-9,6,5
or the given data in cells A1 to A10 can have blank cells substituted for
the zeros
7,,,-4,,,,-9,6,5
What I want to have in cells B1 to B5 is
7,-4,-9,6,5
How can I manipulate the data in A1:A10 to give me the desired output in
B1:B5 ? I want the values in B1:B5 and not in B1, B4, B8, B9 and B10.

Daniel
 
Manual method.

Copy A1:A10 to B1:B10

Select B1:B10

Edit>Replace

what: 0
with: nothing

Checkmark "match entire cell contents"

Replace all.

With B1:B10 still selected F5>Special>Blanks>OK

Edit>Delete>Shift cells up.

Gord Dibben Excel MVP
 
Thanks, Gord. That's what I wanted to do. For the bonus question, I ask you,
I there an automated way to do this in excel?

Daniel

Gord Dibben said:
Manual method.

Copy A1:A10 to B1:B10

Select B1:B10

Edit>Replace

what: 0
with: nothing

Checkmark "match entire cell contents"

Replace all.

With B1:B10 still selected F5>Special>Blanks>OK

Edit>Delete>Shift cells up.

Gord Dibben Excel MVP
Click to expand...
 
Here's the automated version. I am not experienced with
writing VBA code but this is what the macro recorder
generated:

Sub Macro1()

Range("A1:A10").Select
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Replace What:="0", Replacement:="",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp
Range("A1").Select

End Sub

Biff
-----Original Message-----
Thanks, Gord. That's what I wanted to do. For the bonus question, I ask you,
I there an automated way to do this in excel?

Daniel
Click to expand...
 
Daniel

Try this copied to a standard module in your workbook.

Sub test()
Dim rcell As Range
Range("b1:b10").Value = Range("a1:a10").Value
For Each rcell In Range("b1:b10")
If rcell.Value = "" Or rcell.Value = "0" Then
rcell.Delete Shift:=xlUp
End If
Next rcell
End Sub

Gord Dibben Excel MVP

Thanks, Gord. That's what I wanted to do. For the bonus question, I ask you,
I there an automated way to do this in excel?

Daniel
Click to expand...
 
Back
Top