Automatically delete rows with any blank columns
 

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.

Automatically delete rows with any blank columns
 

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Kris" wrote in message
...
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.

Automatically delete rows with any blank columns
 

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.
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Kris" wrote:

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.

Automatically delete rows with any blank columns
 

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?


"Don Guillett" wrote:


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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Kris" wrote in message
...
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.

Automatically delete rows with any blank columns
 

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" wrote:


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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Kris" wrote in message
...
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.

Automatically delete rows with any blank columns
 

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
Microsoft MVP Excel
SalesAid Software

"Kris" wrote in message
...
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?


"Don Guillett" wrote:


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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Kris" wrote in message
...
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.

Automatically delete rows with any blank columns
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Kris" wrote in message
...
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" wrote:


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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Kris" wrote in message
...
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.