Norman's code actually looked at one column to determine if the row is empty.
If you can't pick out a column that is always filled in whenever something in
that row is filled in, you could use something like:
Option Explicit
Public Sub Tester2a()
Dim WB As Workbook
Dim SH As Worksheet
Dim LastRow As Long
Dim FirstRow As Long
Dim iRow As Long
Set WB = ActiveWorkbook '<<==== CHANGE
Set SH = WB.Sheets("Sheet1") '<<==== CHANGE
With SH
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
FirstRow = 1
For iRow = LastRow To FirstRow Step -1
If Application.CountA(.Rows(iRow)) = 0 Then
.Rows(iRow).Delete
End If
Next iRow
End With
End Sub
If you really only wanted to check columns B:M (ignoring A and N:IV):
Option Explicit
Public Sub Tester2a()
Dim WB As Workbook
Dim SH As Worksheet
Dim LastRow As Long
Dim FirstRow As Long
Dim iRow As Long
Set WB = ActiveWorkbook '<<==== CHANGE
Set SH = WB.Sheets("Sheet1") '<<==== CHANGE
With SH
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
FirstRow = 1
For iRow = LastRow To FirstRow Step -1
If Application.CountA _
(.Range(.Cells(iRow, "B"), .Cells(iRow, "M"))) = 0 Then
.Rows(iRow).Delete
End If
Next iRow
End With
End Sub
juergenkemeter wrote:
Hi,
I tried
Code:
--------------------
Public Sub Tester2()
Dim WB As Workbook
Dim SH As Worksheet
Set WB = ActiveWorkbook '<<==== CHANGE
Set SH = WB.Sheets("Sheet1") '<<==== CHANGE
On Error Resume Next
SH.Columns("B:M").SpecialCells(xlBlanks).EntireRow .Delete
On Error GoTo 0
End Sub
--------------------
to delete all rows which have no value in range B to M. Somehow it does
nothing special...
I enclosed an example workbook.
cheers
Juergen
+-------------------------------------------------------------------+
|Filename: DeleteEmptyRow.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4206 |
+-------------------------------------------------------------------+
--
juergenkemeter
------------------------------------------------------------------------
juergenkemeter's Profile: http://www.excelforum.com/member.php...o&userid=25248
View this thread: http://www.excelforum.com/showthread...hreadid=499951
--
Dave Peterson