Deleting a row

C

Chuck Neal

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
 
C

Chip Smith

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...
 
K

Ken Wright

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 :)
------------------------------­------------------------------­----------------
 
C

Chuck Neal

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.
 
K

Ken Hudson

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 > 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("B" & R & ":AB" & R)) _
= 0 Then
Rng.Rows(R).EntireRow.Delete
End If
Next R

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub
 
C

Chuck Neal

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

Ken Hudson

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 > Clear > All.
If you run the macro, does that row get deleted?
 
C

Chuck Neal

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!!!
 
Top