Deleting blank cells in a selection

  • Thread starter Thread starter Novice
  • Start date Start date
N

Novice

Hi,
I am trying to create a macro that will delete blank cells
in a selection. The only code I have found
uses "Selection.SpecialCells
(xlCellTypeBlanks).EntireRow.Delete"

However, I want to delete just the blank cells in a
particulary row if both are blank, not the entire row in
the worksheet.
For example selection is A2:B9 and cells A4 and B4 are
both zero or blank. I need cells A4 and B4 deleted and the
remaining cell selection moved up a row.

Please help! Thanks..
 
Novice,try this and see if it will do what you want
Range("A2:B9").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
 
Thanks for the tip PaulB
But, I can't get it to work...

I run the following macro after highlighting the entire
selection. The selection contains values and there are two
blank cells in the same row in the middle of the two
columns. Now I want the deleted space removed and the
lower portion of data shifted upwards.

Here's the code...


Public Sub DeleteRowOnCell()
On Error Resume Next
Selection.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
ActiveSheet.UsedRange

End Sub
 
Novice, the macro deletes the blanks and moves the data up, I am not sure
what you need
You can also do it without a macro, select the area, edit, goto, special,
blanks, then edit delete

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
 
Are the cells really blank or do you have a formula in them such as

=if(condition,"",formula)

so it only looks blank. This won't work with xlCelltypeblanks, because the
cell isn't really blank.
 
I got the macro to work, but only upon deleting the 0
value in the cells. Is there another command such as
x1CellvalueZero, to recognize the "blank" cells?

Also, in addition, I only want the row of the 2 column
selection deleted if both cells in a given row are zero
values.

Thanks so much!
 
If a cell has zero in it, it isn't blank.


Sub Tester19()
Dim rng As Range
Dim lastrow As Long
Dim firstRow As Long
Dim bEmp As Boolean, bEmp1 As Boolean
Dim col As Long
Set rng = Selection.Columns(1).Cells
lastrow = rng.Rows(rng.Rows.Count).Row
firstRow = rng.Row
col = rng.Column
Set rng = Nothing
For i = lastrow To firstRow Step -1
bEmp = False: bEmp1 = False
If IsNumeric(Cells(i, col)) Then _
If Cells(i, col).Value = 0 Then bEmp = True
If IsEmpty(Cells(i, col)) Then bEmp = True
If IsNumeric(Cells(i, col + 1)) Then _
If Cells(i, col + 1).Value = 0 Then bEmp1 = True
If IsEmpty(Cells(i, col + 1)) Then bEmp1 = True
Cells(i, col - 1).Value = bEmp & bEmp1
If bEmp And bEmp1 Then
If rng Is Nothing Then
Set rng = Cells(i, col).Resize(1, 2)
Else
Set rng = Union(Cells(i, col).Resize(1, 2), rng)
End If
End If
Next
If Not rng Is Nothing Then
rng.Delete Shift:=xlShiftUp
End If

End Sub


--
Regards,
Tom Ogilvy
 
Back
Top