Deleting blank cells in a selection
 

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..

Deleting blank cells in a selection
 

Novice,try this and see if it will do what you want
Range("A2:B9").SpecialCells(xlCellTypeBlanks).Dele te 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 **
"Novice" wrote in message
...
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..

Deleting blank cells in a selection
 

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
-----Original Message-----
Novice,try this and see if it will do what you want
Range("A2:B9").SpecialCells(xlCellTypeBlanks).Dele te
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 **
"Novice" wrote in
message
...
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..


.

Deleting blank cells in a selection
 

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 **
"Novice" wrote in message
...
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
-----Original Message-----
Novice,try this and see if it will do what you want
Range("A2:B9").SpecialCells(xlCellTypeBlanks).Dele te
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 **
"Novice" wrote in
message
...
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..


.

Deleting blank cells in a selection
 

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.

--
Regards,
Tom Ogilvy

Novice wrote in message
...
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
-----Original Message-----
Novice,try this and see if it will do what you want
Range("A2:B9").SpecialCells(xlCellTypeBlanks).Dele te
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 **
"Novice" wrote in
message
...
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..


.

Deleting blank cells in a selection
 

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!


-----Original Message-----
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.

--
Regards,
Tom Ogilvy

Novice wrote in
message
...
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
-----Original Message-----
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
**
"Novice" wrote in
message
...
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..


.



.

Deleting blank cells in a selection
 

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





Novice wrote in message
...
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!


-----Original Message-----
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.

--
Regards,
Tom Ogilvy

Novice wrote in
message
...
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
-----Original Message-----
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
**
"Novice" wrote in
message
...
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..


.



.