Automatically delete rows with any blank columns

[Kris]

I am looking for a way to have Excel automatically delete rows that have
columns that are blank.

Example:

If my row contains 7 columns across and column A & F do not have any data in
them, I want Excel to delete the entire row.

If there is a way to create a macro to do this, I need specific instructions
on how to create a macro, I have very little experience in macros.

I appreciate any and all assistance.

 

[Don Guillett]

You said 7 columns wide but mention a-f which is 6. So, are you saying that
if there is something in any cell in col a,b,c,d,e,or f then leave and if
not delete the whole row. So, if something in col c leave it alone. If so,
use

Sub DeleteRowsifBlanks()
For i = 1 To cells(Rows.Count, 7).End(xlUp).Row
If Application.CountA(cells(i, 1) _
.Resize(, 6)) < 1 Then Rows(i).Delete
Next i
End Sub

 

[Shane Devenshire]

Hi,

Select a column in the area which has the blank rows and press F5, Special,
Blanks, OK. Then press Ctrl+- (control minus). choose Entire Row.

Try the same thing for a row.

 

[Kris]

Don,
I was using an example. My worksheet actually has 5 columns wide and
various rows per worksheet. Would I have to make any corrections to the
macro that you gave given the difference in column size?

 

[Kris]

I tried the info that you included, I copied it and pasted it in and it
didn't work. Some of the columns have drop down lists created to allow the
user to select a quick answer, but a blank is a valid option in the drop down
list, does this make a difference?

I also tried it in cells that do not have the drop down list and it still
wouldn't work.

 

[Don Guillett]

What I gave used the ,7 because you said 7 columns and the ,6 for one less.
Tested. If desired, send your workbook to my address below along with
complete instructions and before/after examples.

 

[Don Guillett]

Sub DeleteRows()
Application.ScreenUpdating = False
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If InStr(Cells(i, 1), ".") < 1 And _
Left(Cells(i, 1), 3) <> "Yes" Then Rows(i).Delete
Next i
Application.ScreenUpdating = True
End Sub