delete rows where that contain an empty cell

G

gerry.lisa

I have 3 columns of data....columns 1 and 2 all have data...where
column 3 contains an empty cell I would like to delete the row.

Can someone help me please??

....Lisa
 
S

Stopher

Try this

Sub Delete_rows()

i = 1
Do While Range("A" & i) <> ""
If Range("C" & i) = "" Then
Range("A" & i).Select
Rows(i).Select
Selection.Delete Shift:=xlUp

Else
i = i + 1
End If
Loop
End Sub

This assumes that your data is in couln A,B,C and the start of the data
is in A1

If your data doesn't start in A1 then change the i=1 to the row that it
does and change the A's to the first coulum letter and the C's to the
third column.

I am assuming you know how to put this code into VBA and run macros.
 
S

starguy

select 3rd column where you have some empty cells and you want to delet
that whole row.
after selecting press F5 to open Go To window, click on special tab an
select Blanks then Ok. this will select all empty cells.
press Ctrl+- and select the option Entire row. this will delete th
entire row where col 3 has empty cell.

hope this would sevre your purpose.
 
G

gerry.lisa

starguy said:
select 3rd column where you have some empty cells and you want to delete
that whole row.
after selecting press F5 to open Go To window, click on special tab and
select Blanks then Ok. this will select all empty cells.
press Ctrl+- and select the option Entire row. this will delete the
entire row where col 3 has empty cell.

hope this would sevre your purpose.
Click to expand...

Stopher, I'm not sure how to do what you suggested.
Starguy, I tried what you said and it seemed to work but then it only
highlighted a few of the blank cells, it didn't hughlight all the
blank cells. After I pressed Ctrl+- and select the option Entire row.
Excel deleted the entire row like I wanted but there are still lots of
rows with a blank cell that it didn't hightlight.

Am I doing something wrong?

....Lisa
 
G

Gord Dibben

If you selected the entire column and some of the "blank" cells did not get
picked up. it may be that these are not blank.

Could have a space or two in the cell.

Could be formulas with error-checking that makes the cell look blank.

In an adjacent column enter =ISBLANK(C1)

Drag/copy down. If the cell is truly blank you will see TRUE, otherwise FALSE.


Gord Dibben MS Excel MVP
 
G

gerry.lisa

Gord said:
If you selected the entire column and some of the "blank" cells did not get
picked up. it may be that these are not blank.

Could have a space or two in the cell.

Could be formulas with error-checking that makes the cell look blank.

In an adjacent column enter =ISBLANK(C1)

Drag/copy down. If the cell is truly blank you will see TRUE, otherwise FALSE.


Gord Dibben MS Excel MVP
Click to expand...

That did it Gord. Thanks!!


....Lisa
 
Top