Delete empty rows.
 

I am looking for a macro to auto-delete rows that are empty between columns
A and N. The main column I am concerned with is B. If B? is empty, delete the
entire row.

Thanks for your time and help,
Steve

Delete empty rows.
 

Hi Steve,

Try:

'=======================
Public Sub Tester02()
On Error Resume Next
Columns("B:B").SpecialCells(xlBlanks). _
EntireRow.Delete
On Error GoTo 0
End Sub
'<<=======================

Alternatively, look at using the AutoFilter featutre.

---
Regards,
Norman



"Steve" wrote in message
...
I am looking for a macro to auto-delete rows that are empty between
columns
A and N. The main column I am concerned with is B. If B? is empty, delete
the
entire row.

Thanks for your time and help,
Steve

Delete empty rows.
 

Hi Steve

Try this for the activesheet usedrange

Sub Example1()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long

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

Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1
If Application.CountA(.Range(.Cells(Lrow, "A"), .Cells(Lrow, "N"))) = 0 Then .Rows(Lrow).Delete
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

More info here
http://www.rondebruin.nl/delete.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Steve" wrote in message ...
I am looking for a macro to auto-delete rows that are empty between columns
A and N. The main column I am concerned with is B. If B? is empty, delete the
entire row.

Thanks for your time and help,
Steve

Delete empty rows.
 

iLastRow = cells(Rows.Count,"B").End(xlUp).Row
For i = iLastRow to 1 Step -1
If Cells(i,"B").Value = "" Then
Rows(i).Delete
End If
Next i

or

iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
Range("B1:B" & iLastRow).SpecialCells(xlCellTypeBlanks).EntireRow .delete


--
HTH

Bob Phillips

"Steve" wrote in message
...
I am looking for a macro to auto-delete rows that are empty between
columns
A and N. The main column I am concerned with is B. If B? is empty, delete
the
entire row.

Thanks for your time and help,
Steve

Delete empty rows.
 

This works great, however, is there a way to limit it to a range instead of
applying it to the entire wksht.

Steve

"Norman Jones" wrote:

Hi Steve,

Try:

'=======================
Public Sub Tester02()
On Error Resume Next
Columns("B:B").SpecialCells(xlBlanks). _
EntireRow.Delete
On Error GoTo 0
End Sub
'<<=======================

Alternatively, look at using the AutoFilter featutre.

---
Regards,
Norman



"Steve" wrote in message
...
I am looking for a macro to auto-delete rows that are empty between
columns
A and N. The main column I am concerned with is B. If B? is empty, delete
the
entire row.

Thanks for your time and help,
Steve

Delete empty rows.
 

Hi Steve Try,

'=======================
Public Sub Tester02()
Dim Rng As Range, Rng1 As Range

Set Rng = Range("A1:K30") '<<====== CHANGE to suit

On Error Resume Next
Set Rng1 = Intersect(Rng, _
Columns("B:B").SpecialCells(xlBlanks))
On Error GoTo 0

If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete

End Sub
'<<=======================


Change address of Rng to limit the rows to be deleted.

---
Regards,
Norman



"Steve" wrote in message
...
This works great, however, is there a way to limit it to a range instead
of
applying it to the entire wksht.

Steve

"Norman Jones" wrote:

Hi Steve,

Try:

'=======================
Public Sub Tester02()
On Error Resume Next
Columns("B:B").SpecialCells(xlBlanks). _
EntireRow.Delete
On Error GoTo 0
End Sub
'<<=======================

Alternatively, look at using the AutoFilter featutre.

---
Regards,
Norman



"Steve" wrote in message
...
I am looking for a macro to auto-delete rows that are empty between
columns
A and N. The main column I am concerned with is B. If B? is empty,
delete
the
entire row.

Thanks for your time and help,
Steve

Delete empty rows.
 

Worked like a charm. Thanks.

"Norman Jones" wrote:

Hi Steve Try,

'=======================
Public Sub Tester02()
Dim Rng As Range, Rng1 As Range

Set Rng = Range("A1:K30") '<<====== CHANGE to suit

On Error Resume Next
Set Rng1 = Intersect(Rng, _
Columns("B:B").SpecialCells(xlBlanks))
On Error GoTo 0

If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete

End Sub
'<<=======================


Change address of Rng to limit the rows to be deleted.

---
Regards,
Norman



"Steve" wrote in message
...
This works great, however, is there a way to limit it to a range instead
of
applying it to the entire wksht.

Steve

"Norman Jones" wrote:

Hi Steve,

Try:

'=======================
Public Sub Tester02()
On Error Resume Next
Columns("B:B").SpecialCells(xlBlanks). _
EntireRow.Delete
On Error GoTo 0
End Sub
'<<=======================

Alternatively, look at using the AutoFilter featutre.

---
Regards,
Norman



"Steve" wrote in message
...
I am looking for a macro to auto-delete rows that are empty between
columns
A and N. The main column I am concerned with is B. If B? is empty,
delete
the
entire row.

Thanks for your time and help,
Steve