software

Hope someone can help with some code. I've read the other examples and I'm
not sure if what I'm trying to do can be accomplished by the others.

A cell in column A has data in it (doesn't matter what the data is). If the
cells in Columns B thru AB are blank, I want to delete the row.

Chuck

are you trying to delete the entire row column a thru ab? is it something you
want to do automatically? otherwise you can clikc on the row to the left,
right click and go to delete row...

--
--Chip Smith--
MVP Wannabe quot;Chuck Nealquot; wrote:

gt; Hope someone can help with some code. I've read the other examples and I'm
gt; not sure if what I'm trying to do can be accomplished by the others.
gt;
gt; A cell in column A has data in it (doesn't matter what the data is). If the
gt; cells in Columns B thru AB are blank, I want to delete the row.
gt;
gt; Chuck

Sure, but do you really need code?

In say col AC use a formula such as =COUNTA(B2:AB2) and copy down. Filter
on col AC for the value 0, then select what you can see, do Edit / Go to /
Special / Visible cells only, and then do Edit / Delete / Entire row. Take
off the filter and you are done.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------

quot;Chuck Nealquot; gt; wrote in message
...
gt; Hope someone can help with some code. I've read the other examples and
gt; I'm
gt; not sure if what I'm trying to do can be accomplished by the others.
gt;
gt; A cell in column A has data in it (doesn't matter what the data is). If
gt; the
gt; cells in Columns B thru AB are blank, I want to delete the row.
gt;
gt; Chuck
Yes, I want to delete the entire row A thru IV. The reason I said AB as the
end column is because I do not have data past there. I want a code because
I'm trying to incorporate it into an existing macro series I'm running. I'm
trying to automate this process as much as possible to avoid human error.

quot;Chip Smithquot; wrote:

gt; are you trying to delete the entire row column a thru ab? is it something you
gt; want to do automatically? otherwise you can clikc on the row to the left,
gt; right click and go to delete row...
gt;
gt; --
gt; --Chip Smith--
gt; MVP Wannabe
gt;
gt;
gt; quot;Chuck Nealquot; wrote:
gt;
gt; gt; Hope someone can help with some code. I've read the other examples and I'm
gt; gt; not sure if what I'm trying to do can be accomplished by the others.
gt; gt;
gt; gt; A cell in column A has data in it (doesn't matter what the data is). If the
gt; gt; cells in Columns B thru AB are blank, I want to delete the row.
gt; gt;
gt; gt; Chuck

Hi Chuck,
I found the following code on Chip Pearson's page and modifed it slightly.
See if this works for you. It counts the number of entries in the B to AB
range and deletes the row if the count is 0.

------------------------
Option Explicit

Public Sub DeleteBlankRows()

Dim R As Long
Dim C As Range
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count gt; 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For R = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Range(quot;Bquot; amp; R amp; quot;:ABquot; amp; R)) _
= 0 Then
Rng.Rows(R).EntireRow.Delete
End If
Next R

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub--
Ken Hudsonquot;Chuck Nealquot; wrote:

gt; Hope someone can help with some code. I've read the other examples and I'm
gt; not sure if what I'm trying to do can be accomplished by the others.
gt;
gt; A cell in column A has data in it (doesn't matter what the data is). If the
gt; cells in Columns B thru AB are blank, I want to delete the row.
gt;
gt; Chuck

I thought for sure that would work because it makes sense, but it didn't.
Thanks though!

quot;Ken Hudsonquot; wrote:

gt; Hi Chuck,
gt; I found the following code on Chip Pearson's page and modifed it slightly.
gt; See if this works for you. It counts the number of entries in the B to AB
gt; range and deletes the row if the count is 0.
gt;
gt; ------------------------
gt; Option Explicit
gt;
gt; Public Sub DeleteBlankRows()
gt;
gt; Dim R As Long
gt; Dim C As Range
gt; Dim Rng As Range
gt;
gt; On Error GoTo EndMacro
gt; Application.ScreenUpdating = False
gt; Application.Calculation = xlCalculationManual
gt;
gt; If Selection.Rows.Count gt; 1 Then
gt; Set Rng = Selection
gt; Else
gt; Set Rng = ActiveSheet.UsedRange.Rows
gt; End If
gt; For R = Rng.Rows.Count To 1 Step -1
gt; If Application.WorksheetFunction.CountA(Rng.Range(quot;Bquot; amp; R amp; quot;:ABquot; amp; R)) _
gt; = 0 Then
gt; Rng.Rows(R).EntireRow.Delete
gt; End If
gt; Next R
gt;
gt; EndMacro:
gt;
gt; Application.ScreenUpdating = True
gt; Application.Calculation = xlCalculationAutomatic
gt;
gt; End Sub
gt;
gt;
gt; --
gt; Ken Hudson
gt;
gt;
gt; quot;Chuck Nealquot; wrote:
gt;
gt; gt; Hope someone can help with some code. I've read the other examples and I'm
gt; gt; not sure if what I'm trying to do can be accomplished by the others.
gt; gt;
gt; gt; A cell in column A has data in it (doesn't matter what the data is). If the
gt; gt; cells in Columns B thru AB are blank, I want to delete the row.
gt; gt;
gt; gt; Chuck

The code works in my test worksheet. Are you certain that B thru AB are truly
empty?
As a test highlight a range from B to AB that you think is empty and then go
to Edit gt; Clear gt; All.
If you run the macro, does that row get deleted?
--
Ken Hudsonquot;Chuck Nealquot; wrote:

gt; I thought for sure that would work because it makes sense, but it didn't.
gt; Thanks though!
gt;
gt; quot;Ken Hudsonquot; wrote:
gt;
gt; gt; Hi Chuck,
gt; gt; I found the following code on Chip Pearson's page and modifed it slightly.
gt; gt; See if this works for you. It counts the number of entries in the B to AB
gt; gt; range and deletes the row if the count is 0.
gt; gt;
gt; gt; ------------------------
gt; gt; Option Explicit
gt; gt;
gt; gt; Public Sub DeleteBlankRows()
gt; gt;
gt; gt; Dim R As Long
gt; gt; Dim C As Range
gt; gt; Dim Rng As Range
gt; gt;
gt; gt; On Error GoTo EndMacro
gt; gt; Application.ScreenUpdating = False
gt; gt; Application.Calculation = xlCalculationManual
gt; gt;
gt; gt; If Selection.Rows.Count gt; 1 Then
gt; gt; Set Rng = Selection
gt; gt; Else
gt; gt; Set Rng = ActiveSheet.UsedRange.Rows
gt; gt; End If
gt; gt; For R = Rng.Rows.Count To 1 Step -1
gt; gt; If Application.WorksheetFunction.CountA(Rng.Range(quot;Bquot; amp; R amp; quot;:ABquot; amp; R)) _
gt; gt; = 0 Then
gt; gt; Rng.Rows(R).EntireRow.Delete
gt; gt; End If
gt; gt; Next R
gt; gt;
gt; gt; EndMacro:
gt; gt;
gt; gt; Application.ScreenUpdating = True
gt; gt; Application.Calculation = xlCalculationAutomatic
gt; gt;
gt; gt; End Sub
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Ken Hudson
gt; gt;
gt; gt;
gt; gt; quot;Chuck Nealquot; wrote:
gt; gt;
gt; gt; gt; Hope someone can help with some code. I've read the other examples and I'm
gt; gt; gt; not sure if what I'm trying to do can be accomplished by the others.
gt; gt; gt;
gt; gt; gt; A cell in column A has data in it (doesn't matter what the data is). If the
gt; gt; gt; cells in Columns B thru AB are blank, I want to delete the row.
gt; gt; gt;
gt; gt; gt; Chuck

I tried again, but ran your code in a different location in my master macro
and it worked great. It actually deleted other rows that I was deleting with
a different macro, so even better! Thanks again!!!

quot;Ken Hudsonquot; wrote:

gt; The code works in my test worksheet. Are you certain that B thru AB are truly
gt; empty?
gt; As a test highlight a range from B to AB that you think is empty and then go
gt; to Edit gt; Clear gt; All.
gt; If you run the macro, does that row get deleted?
gt; --
gt; Ken Hudson
gt;
gt;
gt; quot;Chuck Nealquot; wrote:
gt;
gt; gt; I thought for sure that would work because it makes sense, but it didn't.
gt; gt; Thanks though!
gt; gt;
gt; gt; quot;Ken Hudsonquot; wrote:
gt; gt;
gt; gt; gt; Hi Chuck,
gt; gt; gt; I found the following code on Chip Pearson's page and modifed it slightly.
gt; gt; gt; See if this works for you. It counts the number of entries in the B to AB
gt; gt; gt; range and deletes the row if the count is 0.
gt; gt; gt;
gt; gt; gt; ------------------------
gt; gt; gt; Option Explicit
gt; gt; gt;
gt; gt; gt; Public Sub DeleteBlankRows()
gt; gt; gt;
gt; gt; gt; Dim R As Long
gt; gt; gt; Dim C As Range
gt; gt; gt; Dim Rng As Range
gt; gt; gt;
gt; gt; gt; On Error GoTo EndMacro
gt; gt; gt; Application.ScreenUpdating = False
gt; gt; gt; Application.Calculation = xlCalculationManual
gt; gt; gt;
gt; gt; gt; If Selection.Rows.Count gt; 1 Then
gt; gt; gt; Set Rng = Selection
gt; gt; gt; Else
gt; gt; gt; Set Rng = ActiveSheet.UsedRange.Rows
gt; gt; gt; End If
gt; gt; gt; For R = Rng.Rows.Count To 1 Step -1
gt; gt; gt; If Application.WorksheetFunction.CountA(Rng.Range(quot;Bquot; amp; R amp; quot;:ABquot; amp; R)) _
gt; gt; gt; = 0 Then
gt; gt; gt; Rng.Rows(R).EntireRow.Delete
gt; gt; gt; End If
gt; gt; gt; Next R
gt; gt; gt;
gt; gt; gt; EndMacro:
gt; gt; gt;
gt; gt; gt; Application.ScreenUpdating = True
gt; gt; gt; Application.Calculation = xlCalculationAutomatic
gt; gt; gt;
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; Ken Hudson
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Chuck Nealquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Hope someone can help with some code. I've read the other examples and I'm
gt; gt; gt; gt; not sure if what I'm trying to do can be accomplished by the others.
gt; gt; gt; gt;
gt; gt; gt; gt; A cell in column A has data in it (doesn't matter what the data is). If the
gt; gt; gt; gt; cells in Columns B thru AB are blank, I want to delete the row.
gt; gt; gt; gt;
gt; gt; gt; gt; Chuck