Deleting a row
 

How would I write the code to delete a row depending on if a certain cell is
blank?
If e1 is blank delete row 1 and so on.

range("C").Select
do
row.Delete Shift:=xlUp
loop until isempty(activecell<" ")

Or something like this? I would greatly appreciate the help. Thanks Neal.

Deleting a row
 

Looks like you want to delete ALL the rows that have nothing in column
E. When deleting rows, start from the bottom of the worksheet.

For j=cells(65536,"e").end(xlup).row to 1 step-1
if cells(j,"e")="" then rows(j).delete
next j

This may not be as elegant as some solutions (meaning it might take a
bit longer), but it will work.
James

Deleting a row
 

try
Sub RowBeGone()
Columns("c").SpecialCells(xlCellTypeBlanks).Entire Row.Delete
End Sub

--
Don Guillett
SalesAid Software

"Neal" wrote in message
...
How would I write the code to delete a row depending on if a certain cell
is
blank?
If e1 is blank delete row 1 and so on.

range("C").Select
do
row.Delete Shift:=xlUp
loop until isempty(activecell<" ")

Or something like this? I would greatly appreciate the help. Thanks Neal.

Deleting a row
 

That should be

For j=cells(65535,"e").end(xlup).row to 1 step-1
if cells(j,"e")="" then rows(j).delete
next j

Tallo-ho!
James

Deleting a row
 

I am assuming that I woould replace "e" with c but what would I replace "j"
with? Can I simplify it by just stating row instead of rows"j".delete?

"Zone" wrote:

That should be

For j=cells(65535,"e").end(xlup).row to 1 step-1
if cells(j,"e")="" then rows(j).delete
next j

Tallo-ho!
James

Deleting a row
 

I tried it and nothing happened!

"Don Guillett" wrote:

try
Sub RowBeGone()
Columns("c").SpecialCells(xlCellTypeBlanks).Entire Row.Delete
End Sub

--
Don Guillett
SalesAid Software

"Neal" wrote in message
...
How would I write the code to delete a row depending on if a certain cell
is
blank?
If e1 is blank delete row 1 and so on.

range("C").Select
do
row.Delete Shift:=xlUp
loop until isempty(activecell<" ")

Or something like this? I would greatly appreciate the help. Thanks Neal.

Deleting a row
 

If Don's code did not work for you, then the empty cells are not truly
"Blank". They may have spaces in them which make them appear to be blank.
Also the column you want to check for blanks is vague because you said "If
e1 is blank delete row 1 and so on." and then your example is
"range("C").Select". And then you ask Zone if you can change his "e" to "c".
This indicates you really want to check column C for blanks. If this is true
then I have some code that may work for you. This code will check column "c"
for blanks. If this is not the right column, then feel free to change "c" to
the correct column letter in my code (3 places). Leave the other letters as
is because they are variables needed to run the code.
Also, if spaces are not the reason your cells appear to be blank then this
code will not do the job either because it will check to see if the cell has
nothing else but spaces, and if it does then delete that row. Give it a try
and let us know.

Sub RowBeGone()
Dim j As Long, x As Long, spce As Long
Dim i As Long, cnt As Long

For j = Cells(65535, "c").End(xlUp).Row To 1 Step -1
x = Len(Cells(j, "c"))
If x = 0 Then
Rows(j).Delete
Else
cnt = 0
For i = 1 To x
spce = InStr(i, Cells(j, "c"), " ", 1)
If spce 0 Then cnt = cnt + 1
Next i
End If
If cnt = x Then Rows(j).Delete
Next j
End Sub

Mike F
"Neal" wrote in message
...
I tried it and nothing happened!

"Don Guillett" wrote:

try
Sub RowBeGone()
Columns("c").SpecialCells(xlCellTypeBlanks).Entire Row.Delete
End Sub

--
Don Guillett
SalesAid Software

"Neal" wrote in message
...
How would I write the code to delete a row depending on if a certain
cell
is
blank?
If e1 is blank delete row 1 and so on.

range("C").Select
do
row.Delete Shift:=xlUp
loop until isempty(activecell<" ")

Or something like this? I would greatly appreciate the help. Thanks
Neal.

Deleting a row
 

try this to get spaces and blanks
Sub deleteallblanks()
For i = Cells(Rows.Count, "c").End(xlUp).Row To 2 Step -1
If Cells(i, "c") = " " Or Cells(i, "c") = "" Then Rows(i).Delete
Next i
End Sub

--
Don Guillett
SalesAid Software

"Neal" wrote in message
...
I tried it and nothing happened!

"Don Guillett" wrote:

try
Sub RowBeGone()
Columns("c").SpecialCells(xlCellTypeBlanks).Entire Row.Delete
End Sub

--
Don Guillett
SalesAid Software

"Neal" wrote in message
...
How would I write the code to delete a row depending on if a certain
cell
is
blank?
If e1 is blank delete row 1 and so on.

range("C").Select
do
row.Delete Shift:=xlUp
loop until isempty(activecell<" ")

Or something like this? I would greatly appreciate the help. Thanks
Neal.

Deleting a row
 

Don, this will work if there is only one space in the cell. What if there
are two or more. My code, although not as compact as yours, will compare the
number of spaces to the length of the text, and if they are equal, no matter
how many there are, it will delete the row.
Sound reasonable? If the length is 0 then it will immediately delete the row
without testing for spaces.
Mike F
"Don Guillett" wrote in message
...
try this to get spaces and blanks
Sub deleteallblanks()
For i = Cells(Rows.Count, "c").End(xlUp).Row To 2 Step -1
If Cells(i, "c") = " " Or Cells(i, "c") = "" Then Rows(i).Delete
Next i
End Sub

--
Don Guillett
SalesAid Software

"Neal" wrote in message
...
I tried it and nothing happened!

"Don Guillett" wrote:

try
Sub RowBeGone()
Columns("c").SpecialCells(xlCellTypeBlanks).Entire Row.Delete
End Sub

--
Don Guillett
SalesAid Software

"Neal" wrote in message
...
How would I write the code to delete a row depending on if a certain
cell
is
blank?
If e1 is blank delete row 1 and so on.

range("C").Select
do
row.Delete Shift:=xlUp
loop until isempty(activecell<" ")

Or something like this? I would greatly appreciate the help. Thanks
Neal.

Deleting a row
 

I think OP said "blank" so it's unlikely there would be more than one space.

--
Don Guillett
SalesAid Software

"Mike Fogleman" wrote in message
...
Don, this will work if there is only one space in the cell. What if there
are two or more. My code, although not as compact as yours, will compare
the number of spaces to the length of the text, and if they are equal, no
matter how many there are, it will delete the row.
Sound reasonable? If the length is 0 then it will immediately delete the
row without testing for spaces.
Mike F
"Don Guillett" wrote in message
...
try this to get spaces and blanks
Sub deleteallblanks()
For i = Cells(Rows.Count, "c").End(xlUp).Row To 2 Step -1
If Cells(i, "c") = " " Or Cells(i, "c") = "" Then Rows(i).Delete
Next i
End Sub

--
Don Guillett
SalesAid Software

"Neal" wrote in message
...
I tried it and nothing happened!

"Don Guillett" wrote:

try
Sub RowBeGone()
Columns("c").SpecialCells(xlCellTypeBlanks).Entire Row.Delete
End Sub

--
Don Guillett
SalesAid Software

"Neal" wrote in message
...
How would I write the code to delete a row depending on if a certain
cell
is
blank?
If e1 is blank delete row 1 and so on.

range("C").Select
do
row.Delete Shift:=xlUp
loop until isempty(activecell<" ")

Or something like this? I would greatly appreciate the help. Thanks
Neal.

Deleting a row
 

Sub deleteRowsWithBlanksInC()
For i = Cells(Rows.Count, "c").End(xlUp).Row To 2 Step -1
If len(trim(Cells(i, "c"))) = 0 Then Rows(i).Delete
Next i
End Sub

would be more robust.

--
Regards,
Tom Ogilvy


"Don Guillett" wrote in message
...
I think OP said "blank" so it's unlikely there would be more than one
space.

--
Don Guillett
SalesAid Software

"Mike Fogleman" wrote in message
...
Don, this will work if there is only one space in the cell. What if
there
are two or more. My code, although not as compact as yours, will compare
the number of spaces to the length of the text, and if they are equal,
no
matter how many there are, it will delete the row.
Sound reasonable? If the length is 0 then it will immediately delete the
row without testing for spaces.
Mike F
"Don Guillett" wrote in message
...
try this to get spaces and blanks
Sub deleteallblanks()
For i = Cells(Rows.Count, "c").End(xlUp).Row To 2 Step -1
If Cells(i, "c") = " " Or Cells(i, "c") = "" Then Rows(i).Delete
Next i
End Sub

--
Don Guillett
SalesAid Software

"Neal" wrote in message
...
I tried it and nothing happened!

"Don Guillett" wrote:

try
Sub RowBeGone()
Columns("c").SpecialCells(xlCellTypeBlanks).Entire Row.Delete
End Sub

--
Don Guillett
SalesAid Software

"Neal" wrote in message
...
How would I write the code to delete a row depending on if a
certain
cell
is
blank?
If e1 is blank delete row 1 and so on.

range("C").Select
do
row.Delete Shift:=xlUp
loop until isempty(activecell<" ")

Or something like this? I would greatly appreciate the help. Thanks
Neal.

Deleting a row
 

Agreed.


--
Don Guillett
SalesAid Software

"Tom Ogilvy" wrote in message
...


Sub deleteRowsWithBlanksInC()
For i = Cells(Rows.Count, "c").End(xlUp).Row To 2 Step -1
If len(trim(Cells(i, "c"))) = 0 Then Rows(i).Delete
Next i
End Sub

would be more robust.

--
Regards,
Tom Ogilvy


"Don Guillett" wrote in message
...
I think OP said "blank" so it's unlikely there would be more than one
space.

--
Don Guillett
SalesAid Software

"Mike Fogleman" wrote in message
...
Don, this will work if there is only one space in the cell. What if
there
are two or more. My code, although not as compact as yours, will
compare
the number of spaces to the length of the text, and if they are equal,
no
matter how many there are, it will delete the row.
Sound reasonable? If the length is 0 then it will immediately delete
the
row without testing for spaces.
Mike F
"Don Guillett" wrote in message
...
try this to get spaces and blanks
Sub deleteallblanks()
For i = Cells(Rows.Count, "c").End(xlUp).Row To 2 Step -1
If Cells(i, "c") = " " Or Cells(i, "c") = "" Then Rows(i).Delete
Next i
End Sub

--
Don Guillett
SalesAid Software

"Neal" wrote in message
...
I tried it and nothing happened!

"Don Guillett" wrote:

try
Sub RowBeGone()
Columns("c").SpecialCells(xlCellTypeBlanks).Entire Row.Delete
End Sub

--
Don Guillett
SalesAid Software

"Neal" wrote in message
...
How would I write the code to delete a row depending on if a
certain
cell
is
blank?
If e1 is blank delete row 1 and so on.

range("C").Select
do
row.Delete Shift:=xlUp
loop until isempty(activecell<" ")

Or something like this? I would greatly appreciate the help.
Thanks
Neal.

Deleting a row
 

Tom, oddly enough, in my test sheet if I inserted 2 spaces into the Blank
cells, then did Trim function and paste special values, Don's code still did
not work, telling me the cells are still not truly blank! Only when I
Cleared Contents did Don's code work. Hmmm.

Mike F

"Don Guillett" wrote in message
...
Agreed.


--
Don Guillett
SalesAid Software

"Tom Ogilvy" wrote in message
...


Sub deleteRowsWithBlanksInC()
For i = Cells(Rows.Count, "c").End(xlUp).Row To 2 Step -1
If len(trim(Cells(i, "c"))) = 0 Then Rows(i).Delete
Next i
End Sub

would be more robust.

--
Regards,
Tom Ogilvy


"Don Guillett" wrote in message
...
I think OP said "blank" so it's unlikely there would be more than one
space.

--
Don Guillett
SalesAid Software

"Mike Fogleman" wrote in message
...
Don, this will work if there is only one space in the cell. What if
there
are two or more. My code, although not as compact as yours, will
compare
the number of spaces to the length of the text, and if they are equal,
no
matter how many there are, it will delete the row.
Sound reasonable? If the length is 0 then it will immediately delete
the
row without testing for spaces.
Mike F
"Don Guillett" wrote in message
...
try this to get spaces and blanks
Sub deleteallblanks()
For i = Cells(Rows.Count, "c").End(xlUp).Row To 2 Step -1
If Cells(i, "c") = " " Or Cells(i, "c") = "" Then Rows(i).Delete
Next i
End Sub

--
Don Guillett
SalesAid Software

"Neal" wrote in message
...
I tried it and nothing happened!

"Don Guillett" wrote:

try
Sub RowBeGone()
Columns("c").SpecialCells(xlCellTypeBlanks).Entire Row.Delete
End Sub

--
Don Guillett
SalesAid Software

"Neal" wrote in message
...
How would I write the code to delete a row depending on if a
certain
cell
is
blank?
If e1 is blank delete row 1 and so on.

range("C").Select
do
row.Delete Shift:=xlUp
loop until isempty(activecell<" ")

Or something like this? I would greatly appreciate the help.
Thanks
Neal.

Deleting a row
 

I don't follow exactly what you did, but if you put
=""
in a cell, then do edit=copy and Edit=paste Special and select values, the
cell will have a len of zero but will not be empty.

Therefore SpecialCells(xlBlanks) will not pick it up since it isn't blank.

--
Regards,
Tom Ogilvy




"Mike Fogleman" wrote in message
...
Tom, oddly enough, in my test sheet if I inserted 2 spaces into the Blank
cells, then did Trim function and paste special values, Don's code still
did
not work, telling me the cells are still not truly blank! Only when I
Cleared Contents did Don's code work. Hmmm.

Mike F

"Don Guillett" wrote in message
...
Agreed.


--
Don Guillett
SalesAid Software

"Tom Ogilvy" wrote in message
...


Sub deleteRowsWithBlanksInC()
For i = Cells(Rows.Count, "c").End(xlUp).Row To 2 Step -1
If len(trim(Cells(i, "c"))) = 0 Then Rows(i).Delete
Next i
End Sub

would be more robust.

--
Regards,
Tom Ogilvy


"Don Guillett" wrote in message
...
I think OP said "blank" so it's unlikely there would be more than one
space.

--
Don Guillett
SalesAid Software

"Mike Fogleman" wrote in message
...
Don, this will work if there is only one space in the cell. What if
there
are two or more. My code, although not as compact as yours, will
compare
the number of spaces to the length of the text, and if they are
equal,
no
matter how many there are, it will delete the row.
Sound reasonable? If the length is 0 then it will immediately delete
the
row without testing for spaces.
Mike F
"Don Guillett" wrote in message
...
try this to get spaces and blanks
Sub deleteallblanks()
For i = Cells(Rows.Count, "c").End(xlUp).Row To 2 Step -1
If Cells(i, "c") = " " Or Cells(i, "c") = "" Then Rows(i).Delete
Next i
End Sub

--
Don Guillett
SalesAid Software

"Neal" wrote in message
...
I tried it and nothing happened!

"Don Guillett" wrote:

try
Sub RowBeGone()
Columns("c").SpecialCells(xlCellTypeBlanks).Entire Row.Delete
End Sub

--
Don Guillett
SalesAid Software

"Neal" wrote in message
...
How would I write the code to delete a row depending on if a
certain
cell
is
blank?
If e1 is blank delete row 1 and so on.

range("C").Select
do
row.Delete Shift:=xlUp
loop until isempty(activecell<" ")

Or something like this? I would greatly appreciate the help.
Thanks
Neal.

Deleting a row
 

or accidentally touching the space bar will do it

--
Don Guillett
SalesAid Software

"Tom Ogilvy" wrote in message
...
I don't follow exactly what you did, but if you put
=""
in a cell, then do edit=copy and Edit=paste Special and select values,
the
cell will have a len of zero but will not be empty.

Therefore SpecialCells(xlBlanks) will not pick it up since it isn't blank.

--
Regards,
Tom Ogilvy




"Mike Fogleman" wrote in message
...
Tom, oddly enough, in my test sheet if I inserted 2 spaces into the Blank
cells, then did Trim function and paste special values, Don's code still
did
not work, telling me the cells are still not truly blank! Only when I
Cleared Contents did Don's code work. Hmmm.

Mike F

"Don Guillett" wrote in message
...
Agreed.


--
Don Guillett
SalesAid Software

"Tom Ogilvy" wrote in message
...


Sub deleteRowsWithBlanksInC()
For i = Cells(Rows.Count, "c").End(xlUp).Row To 2 Step -1
If len(trim(Cells(i, "c"))) = 0 Then Rows(i).Delete
Next i
End Sub

would be more robust.

--
Regards,
Tom Ogilvy


"Don Guillett" wrote in message
...
I think OP said "blank" so it's unlikely there would be more than one
space.

--
Don Guillett
SalesAid Software

"Mike Fogleman" wrote in message
...
Don, this will work if there is only one space in the cell. What if
there
are two or more. My code, although not as compact as yours, will
compare
the number of spaces to the length of the text, and if they are
equal,
no
matter how many there are, it will delete the row.
Sound reasonable? If the length is 0 then it will immediately
delete
the
row without testing for spaces.
Mike F
"Don Guillett" wrote in message
...
try this to get spaces and blanks
Sub deleteallblanks()
For i = Cells(Rows.Count, "c").End(xlUp).Row To 2 Step -1
If Cells(i, "c") = " " Or Cells(i, "c") = "" Then Rows(i).Delete
Next i
End Sub

--
Don Guillett
SalesAid Software

"Neal" wrote in message
...
I tried it and nothing happened!

"Don Guillett" wrote:

try
Sub RowBeGone()
Columns("c").SpecialCells(xlCellTypeBlanks).Entire Row.Delete
End Sub

--
Don Guillett
SalesAid Software

"Neal" wrote in message
...
How would I write the code to delete a row depending on if a
certain
cell
is
blank?
If e1 is blank delete row 1 and so on.

range("C").Select
do
row.Delete Shift:=xlUp
loop until isempty(activecell<" ")

Or something like this? I would greatly appreciate the help.
Thanks
Neal.

Deleting a row
 

That will put a space in the cell, but not what I am talking about.

--
Regards,
Tom Ogilvy

"Don Guillett" wrote in message
...
or accidentally touching the space bar will do it

--
Don Guillett
SalesAid Software

"Tom Ogilvy" wrote in message
...
I don't follow exactly what you did, but if you put
=""
in a cell, then do edit=copy and Edit=paste Special and select values,
the
cell will have a len of zero but will not be empty.

Therefore SpecialCells(xlBlanks) will not pick it up since it isn't
blank.

--
Regards,
Tom Ogilvy




"Mike Fogleman" wrote in message
...
Tom, oddly enough, in my test sheet if I inserted 2 spaces into the
Blank
cells, then did Trim function and paste special values, Don's code
still
did
not work, telling me the cells are still not truly blank! Only when I
Cleared Contents did Don's code work. Hmmm.

Mike F

"Don Guillett" wrote in message
...
Agreed.


--
Don Guillett
SalesAid Software

"Tom Ogilvy" wrote in message
...


Sub deleteRowsWithBlanksInC()
For i = Cells(Rows.Count, "c").End(xlUp).Row To 2 Step -1
If len(trim(Cells(i, "c"))) = 0 Then Rows(i).Delete
Next i
End Sub

would be more robust.

--
Regards,
Tom Ogilvy


"Don Guillett" wrote in message
...
I think OP said "blank" so it's unlikely there would be more than
one
space.

--
Don Guillett
SalesAid Software

"Mike Fogleman" wrote in message
...
Don, this will work if there is only one space in the cell. What
if
there
are two or more. My code, although not as compact as yours, will
compare
the number of spaces to the length of the text, and if they are
equal,
no
matter how many there are, it will delete the row.
Sound reasonable? If the length is 0 then it will immediately
delete
the
row without testing for spaces.
Mike F
"Don Guillett" wrote in message
...
try this to get spaces and blanks
Sub deleteallblanks()
For i = Cells(Rows.Count, "c").End(xlUp).Row To 2 Step -1
If Cells(i, "c") = " " Or Cells(i, "c") = "" Then Rows(i).Delete
Next i
End Sub

--
Don Guillett
SalesAid Software

"Neal" wrote in message
...
I tried it and nothing happened!

"Don Guillett" wrote:

try
Sub RowBeGone()
Columns("c").SpecialCells(xlCellTypeBlanks).Entire Row.Delete
End Sub

--
Don Guillett
SalesAid Software

"Neal" wrote in message
...
How would I write the code to delete a row depending on if a
certain
cell
is
blank?
If e1 is blank delete row 1 and so on.

range("C").Select
do
row.Delete Shift:=xlUp
loop until isempty(activecell<" ")

Or something like this? I would greatly appreciate the help.
Thanks
Neal.