Delete every row if the cells in first 12 columns are empty

  • Thread starter Thread starter ArgarLargar
  • Start date Start date
A

ArgarLargar

Hi,

I found some VBA code in this group which I have modified below. I
have not been able to get this to work in Excel 2003.

Option Explicit


Sub DelEmptyRow()
Dim ws As Worksheet, rw As Range, c1 As Range
Set ws = ActiveSheet
' Cycle through first 2000 rows
For Each rw In ws.Range("1:2000").Rows
' first cell in the row
Set c1 = rw.Cells(1)
' test if first cell is empty,
' and so do the rest of cells in the row
If c1.Formula = "" And c1.End(xlToRight).Column = 12 Then
rw.Delete
End If
Next rw
End Sub

I have more than 50,000 rows that need this.

Any suggestions?

Thanks,

Nick
 
Hi Nick

From
http://www.rondebruin.nl/delete.htm

You can also use:

With this if you want to fill in the Firstrow and the Lastrow yourself.

Firstrow = 4
Lastrow = 100

Or with this if you want to fill in the Firstrow and let the code find the Lastrow in the column.

Firstrow = 1
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row


Try this example that loop through all rows with data in the worksheet

Sub Loop_Example()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'We use the ActiveSheet but you can replace this with
'Sheets("MySheet")if you want
With ActiveSheet

'We select the sheet so we can change the window view
.Select

'If you are in Page Break Preview Or Page Layout view go
'back to normal view, we do this for speed
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

'Turn off Page Breaks, we do this for speed
.DisplayPageBreaks = False

'Set the first and last row to loop through
Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

'We loop from Lastrow to Firstrow (bottom to top)
For Lrow = Lastrow To Firstrow Step -1

If Application.CountA(.Range(.Cells(Lrow, "A"), _
.Cells(Lrow, "L"))) = 0 Then .Rows(Lrow).Delete

Next Lrow

End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub
 
I found some VBA code in this group which I have modified below. I
have not been able to get this to work in Excel 2003.
Click to expand...

As always, it REALLY helps if you mention HOW it doesn't work. Syntax error,
runtime error? No errors but doesn't do what you expect?
Option Explicit

Sub DelEmptyRow()
Dim ws As Worksheet, rw As Range, c1 As Range
Set ws = ActiveSheet
' Cycle through first 2000 rows
For Each rw In ws.Range("1:2000").Rows
' first cell in the row
Set c1 = rw.Cells(1)
' test if first cell is empty,
' and so do the rest of cells in the row
If c1.Formula = "" And c1.End(xlToRight).Column = 12 Then
Click to expand...

Given your specs, this is a problem. If col A in the current row were blank,
then .End(xlToRight) would refer to the first cell to the right that's NOT
blank, and if that cell were in col 12 (aka L), then only the first 11 cells
in the row would be blank, not the first 12. Change the 12 to 13.
rw.Delete
End If
Next rw
End Sub
Click to expand...

I suspect another problem is going through the range top to bottom, which is
what For Each would do. When deleting rows, it's ALWAYS safer to go through
the range bottom to top.

Dim i As Long
:
For i = 2000 To 1 Step -1
Set rw = ws.Range("1:2000").Rows(i)
:
Next i

The problem with going top to bottom while deleting is that the For Each
loop won't adjust for the deletion. If row 123 met the criteria for
deletion, once you've deleted it, the row that had been 124 would become row
123, but the For Each loop will blissfully advance you to the new row 124,
which had been row 125 just before you deleted row 123, so it won't process
the row that had been row 124 just before you deleted row 123. OTOH, it will
happily process rows that had been below row 2000 before you ran the macro.

Going bottom to top, whether you delete the current iteration's row or not,
you're always processing the row above it in the next iteration, and you're
never processing rows that had been outside the original range when the
macro started.
 
If you're only doing this once rather than repeatedly, you don't need a
macro. Use an AutoFilter. In the column just to the right of your table,
which I'll assume is in A1:Z50000 with headings in row 1, enter the formula

AA2:
=COUNTA(A2:L2)=0

Fill AA2 down into AA3:AA50000. Select A1:AA50000, run Data > Filter >
AutoFilter. Then select TRUE from the drop-down list for col AA, which
should filter out only the rows in which cols A to L are blank, press
[Shift]+[SpaceBar] to select entire rows, then run Edit > Delete to delete
those rows. Run Data > Filter > AutoFilter again to clear the AutoFilter,
then clear col AA.
 
Thanks for the assistance.

Harlan,
About the error. . .nothing happened when I selected the macro to
run. All I saw was my regular worksheet screen there and my cursor.
I can see the screen flashing a bit when I my macros to handle tons of
data.

Ron,
Thanks as well.
 
Back
Top