I need help with code to delete all rows for which column P is not empty.
Thanks in advance.. Kelli

Hi Kelly,

Try:
'================
Public Sub Tester()
Dim rng As Range
Dim rCell As Range
Dim delRng As Range
Dim WB As Workbook
Dim SH As Worksheet
Dim CalcMode As Long

Set WB = ActiveWorkbook '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE

Set rng = Intersect(SH.UsedRange, SH.Columns("P:P"))

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

For Each rCell In rng.Cells
If Not IsEmpty(rCell) Then
If delRng Is Nothing Then
Set delRng = rCell
Else
Set delRng = Union(rCell, delRng)
End If
End If
Next rCell

If Not delRng Is Nothing Then
delRng.EntireRow.Delete
End If

With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

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

---
Regards,
Norman



"KellyInCali" wrote in message
...
I need help with code to delete all rows for which column P is not empty.
Thanks in advance.. Kelli

Thanks Norman! I tried it, but it deletes ALL the rows, even if P is null.

-Kelly

"Norman Jones" wrote:

Hi Kelly,

Try:
'================
Public Sub Tester()
Dim rng As Range
Dim rCell As Range
Dim delRng As Range
Dim WB As Workbook
Dim SH As Worksheet
Dim CalcMode As Long

Set WB = ActiveWorkbook '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE

Set rng = Intersect(SH.UsedRange, SH.Columns("P:P"))

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

For Each rCell In rng.Cells
If Not IsEmpty(rCell) Then
If delRng Is Nothing Then
Set delRng = rCell
Else
Set delRng = Union(rCell, delRng)
End If
End If
Next rCell

If Not delRng Is Nothing Then
delRng.EntireRow.Delete
End If

With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

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

---
Regards,
Norman



"KellyInCali" wrote in message
...
I need help with code to delete all rows for which column P is not empty.
Thanks in advance.. Kelli

Hi Kelli,

Thanks Norman! I tried it, but it deletes ALL the rows, even if P is
null.

Your request was to delete all rows where column P is populated:

I need help with code to delete all rows for which column P is not empty

That is what the suggested code does. The line:

If Not IsEmpty(rCell) Then

ensures that rows will not be deleted if the corresponding column P cell is
empty.

If, therefore, this is not your experience, it would seem likely that the
'null' cells are not, in fact, empty; perhaps these cells contain a formula
which returns an empty string, or perhaps the cells appear empty but contain
an apostrophe.

I would suggest, therefore, that you check the 'null' cells to verify their
contents.

If you still experience problems, post back with additional information
about the contentious column P cells.


---
Regards,
Norman


"KellyInCali" wrote in message
...
Thanks Norman! I tried it, but it deletes ALL the rows, even if P is
null.

-Kelly

"Norman Jones" wrote:

Hi Kelly,

Try:
'================
Public Sub Tester()
Dim rng As Range
Dim rCell As Range
Dim delRng As Range
Dim WB As Workbook
Dim SH As Worksheet
Dim CalcMode As Long

Set WB = ActiveWorkbook '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE

Set rng = Intersect(SH.UsedRange, SH.Columns("P:P"))

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

For Each rCell In rng.Cells
If Not IsEmpty(rCell) Then
If delRng Is Nothing Then
Set delRng = rCell
Else
Set delRng = Union(rCell, delRng)
End If
End If
Next rCell

If Not delRng Is Nothing Then
delRng.EntireRow.Delete
End If

With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

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

---
Regards,
Norman



"KellyInCali" wrote in message
...
I need help with code to delete all rows for which column P is not
empty.
Thanks in advance.. Kelli

or possibly, they contain a space. I have a coworker who likes to delete
data by just using the spacebar.

"Norman Jones" wrote:

Hi Kelli,

Thanks Norman! I tried it, but it deletes ALL the rows, even if P is
null.

Your request was to delete all rows where column P is populated:

I need help with code to delete all rows for which column P is not empty

That is what the suggested code does. The line:

If Not IsEmpty(rCell) Then

ensures that rows will not be deleted if the corresponding column P cell is
empty.

If, therefore, this is not your experience, it would seem likely that the
'null' cells are not, in fact, empty; perhaps these cells contain a formula
which returns an empty string, or perhaps the cells appear empty but contain
an apostrophe.

I would suggest, therefore, that you check the 'null' cells to verify their
contents.

If you still experience problems, post back with additional information
about the contentious column P cells.


---
Regards,
Norman


"KellyInCali" wrote in message
...
Thanks Norman! I tried it, but it deletes ALL the rows, even if P is
null.

-Kelly

"Norman Jones" wrote:

Hi Kelly,

Try:
'================
Public Sub Tester()
Dim rng As Range
Dim rCell As Range
Dim delRng As Range
Dim WB As Workbook
Dim SH As Worksheet
Dim CalcMode As Long

Set WB = ActiveWorkbook '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE

Set rng = Intersect(SH.UsedRange, SH.Columns("P:P"))

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

For Each rCell In rng.Cells
If Not IsEmpty(rCell) Then
If delRng Is Nothing Then
Set delRng = rCell
Else
Set delRng = Union(rCell, delRng)
End If
End If
Next rCell

If Not delRng Is Nothing Then
delRng.EntireRow.Delete
End If

With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

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

---
Regards,
Norman



"KellyInCali" wrote in message
...
I need help with code to delete all rows for which column P is not
empty.
Thanks in advance.. Kelli

Hi J,

or possibly, they contain a space. I have a coworker who likes to delete
data by just using the spacebar

Quite correct.

If Kelli's rows are being deleted, the the cells that she describes as
'null' are not empty and your space suggestion represents a very real
possibility.


---
Regards,
Norman



"JMB" wrote in message
...
or possibly, they contain a space. I have a coworker who likes to delete
data by just using the spacebar.

K, Try
Dim n As Long, xxxxx As Long, mob As Long
With ActiveSheet
xxxxx = Cells(Rows.Count, "a").End(xlUp).Row
For n = 1 To xxxxx Step 1
If Cells(n, "p").Value = "" Then
Cells(n, "P").Value = "mob"
End If
Next n
For n = xxxxx To 1 Step -1
If Cells(n, "P") = "mob" Then
..Rows(n).Delete

End If
Next n
End With
End Sub


--
knowtrump
------------------------------------------------------------------------
knowtrump's Profile: http://www.excelforum.com/member.php...o&userid=19664
View this thread: http://www.excelforum.com/showthread...hreadid=512949