G
Guest
I need help with code to delete all rows for which column P is not empty.
Thanks in advance.. Kelli
Thanks in advance.. Kelli
N
Norman Jones
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
"))
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
'<<================
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
"))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
'<<================
G
Guest
Thanks Norman! I tried it, but it deletes ALL the rows, even if P is null.
-Kelly
-Kelly
N
Norman Jones
Hi Kelli,
Your request was to delete all rows where column P is populated:
That is what the suggested code does. The line:
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.
Your request was to delete all rows where column P is populated:
That is what the suggested code does. The line:
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.
G
Guest
or possibly, they contain a space. I have a coworker who likes to delete
data by just using the spacebar.
data by just using the spacebar.
N
Norman Jones
Hi J,
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.
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.
G
Guest
your space suggestion represents a very real possibility.
One that I've experienced several times. My co-workers response:
"But there's nothing on the screen!" <g>
One that I've experienced several times. My co-workers response:
"But there's nothing on the screen!" <g>
B
broro183
Hi Kelli,
My first check would be that there is no conditional or other
formatting that results in the data being invisible in column P.
A couple of easy ways of quickly checking if there is anything in any
of the problem cells would be to insert a temporary column next to
column P & type in (for as many rows as needed):
= if(isblank(P1),"empty cell","something is in here")
or
= if(len(P1)=0,"empty cell","something is in here")
re spaces:" "But there's nothing on the screen!" <g> "
Yep, I've had this too - good ol' workmates eh?
Rob Brockett
NZ
Always learning & the best way to learn is to experience...
My first check would be that there is no conditional or other
formatting that results in the data being invisible in column P.
A couple of easy ways of quickly checking if there is anything in any
of the problem cells would be to insert a temporary column next to
column P & type in (for as many rows as needed):
= if(isblank(P1),"empty cell","something is in here")
or
= if(len(P1)=0,"empty cell","something is in here")
re spaces:" "But there's nothing on the screen!" <g> "
Yep, I've had this too - good ol' workmates eh?
Rob Brockett
NZ
Always learning & the best way to learn is to experience...
K
knowtrump
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
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
G
Guest
loL! I may be blonde and clueless about VBA, but I'm not totally stupid..
No, I don't delete with my spacebar. However, there used to be a if
statement in P which returns either "DELETE" or "". When the calculations
are done, to get rid of the formulas my existing macro does a
copy/paste-value. I had already thought that maybe it was leaving an
antinull (thanks broro!) and you are right. It's not a space, so I don't
know exactly what's there, but if I clear contents of the "empty" cell, your
code works (Norman). So, I guess I have to include something that truly
empties the non-empty empty cells? Does knowtrumps code do that?
-Tks guys!
No, I don't delete with my spacebar. However, there used to be a if
statement in P which returns either "DELETE" or "". When the calculations
are done, to get rid of the formulas my existing macro does a
copy/paste-value. I had already thought that maybe it was leaving an
antinull (thanks broro!) and you are right. It's not a space, so I don't
know exactly what's there, but if I clear contents of the "empty" cell, your
code works (Norman). So, I guess I have to include something that truly
empties the non-empty empty cells? Does knowtrumps code do that?
-Tks guys!
N
Norman Jones
Hi Kelli,
Try this adaptation:
'================>>
Public Sub Tester2()
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"))
On Error GoTo XIT
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
For Each rCell In rng.Cells
If Not IsEmpty(rCell) And rCell.Value <> "" 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
XIT:
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With
End Sub
'<<================
Try this adaptation:
'================>>
Public Sub Tester2()
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
"))On Error GoTo XIT
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
For Each rCell In rng.Cells
If Not IsEmpty(rCell) And rCell.Value <> "" 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
XIT:
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With
End Sub
'<<================
G
Guest
ThankyouThankyouThankyou Norman! After a small addition to save my header
row, this works perf! Have a great day! -kelli
row, this works perf! Have a great day! -kelli
G
Guest
Norman... If it's not too presumptuous of me, may I ask one more favor? I
now just need to select and copy all rows for which B is populated. At this
point, all the records with data in B are grouped and there are no empty rows
after the last record. You've been great and I really appreciate the help!
-kelli
now just need to select and copy all rows for which B is populated. At this
point, all the records with data in B are grouped and there are no empty rows
after the last record. You've been great and I really appreciate the help!
-kelli
N
Norman Jones
Hi Kelli,
Try:
'================>>
Public Sub Tester3()
Dim WB As Workbook
Dim SH As Worksheet
Dim FirstCell As Range
Dim LastCell As Range
Dim destRng As Range
Set WB = ActiveWorkbook '<<== CHANGE
Set SH = WB.Sheets("Sheet1") '<<== CHANGE
Set destRng = WB.Sheets("Sheet2").Range("A1") '<<== CHANGE
If Not IsEmpty(SH.Range("B1")) Then
Set FirstCell = SH.Range("B1")
Else
Set FirstCell = SH.Range("B1").End(xlDown)
End If
Set LastCell = SH.Cells(Rows.Count, "B").End(xlUp)
SH.Range(FirstCell, LastCell).Copy Destination:=destRng
End Sub
'<<================
Change the destRng to suit your requirements.
If the header row is not to be copied, change B1 to B2.
Try:
'================>>
Public Sub Tester3()
Dim WB As Workbook
Dim SH As Worksheet
Dim FirstCell As Range
Dim LastCell As Range
Dim destRng As Range
Set WB = ActiveWorkbook '<<== CHANGE
Set SH = WB.Sheets("Sheet1") '<<== CHANGE
Set destRng = WB.Sheets("Sheet2").Range("A1") '<<== CHANGE
If Not IsEmpty(SH.Range("B1")) Then
Set FirstCell = SH.Range("B1")
Else
Set FirstCell = SH.Range("B1").End(xlDown)
End If
Set LastCell = SH.Cells(Rows.Count, "B").End(xlUp)
SH.Range(FirstCell, LastCell).Copy Destination:=destRng
End Sub
'<<================
Change the destRng to suit your requirements.
If the header row is not to be copied, change B1 to B2.
N
Norman Jones
Hi Kelli,
Change:
to
SH.Range(FirstCell, LastCell).EntireRow.Copy _
Destination:=destRng
Change:
to
SH.Range(FirstCell, LastCell).EntireRow.Copy _
Destination:=destRng
G
Guest
Thanks! Is a destination required or did you just throw that in for
convenience? I will actually be pasting values only to somewhere else
entirely. Will it mess it up to remove the destRng references? -kelli
convenience? I will actually be pasting values only to somewhere else
entirely. Will it mess it up to remove the destRng references? -kelli
N
Norman Jones
Hi Kelli,
The destRng variable is used to represent that "somewhere else"
However, if you want to copy values only, try instead:
'================>>
Public Sub Tester4()
Dim WB As Workbook
Dim SH As Worksheet
Dim FirstCell As Range
Dim LastCell As Range
Dim destRng As Range
Set WB = ActiveWorkbook '<<=== CHANGE
Set SH = WB.Sheets("Sheet1") '<<=== CHANGE
Set destRng = WB.Sheets("Sheet2").Range("A1") '<<=== CHANGE
If Not IsEmpty(SH.Range("B2")) Then
Set FirstCell = SH.Range("B2")
Else
Set FirstCell = SH.Range("B2").End(xlDown)
End If
Set LastCell = SH.Cells(Rows.Count, "B").End(xlUp)
SH.Range(FirstCell, LastCell).EntireRow.Copy
destRng.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlPasteSpecialOperationNone, _
SkipBlanks:=False, _
Transpose:=False
Application.CutCopyMode = False
End Sub
'<<================
The destRng variable is used to represent that "somewhere else"
However, if you want to copy values only, try instead:
'================>>
Public Sub Tester4()
Dim WB As Workbook
Dim SH As Worksheet
Dim FirstCell As Range
Dim LastCell As Range
Dim destRng As Range
Set WB = ActiveWorkbook '<<=== CHANGE
Set SH = WB.Sheets("Sheet1") '<<=== CHANGE
Set destRng = WB.Sheets("Sheet2").Range("A1") '<<=== CHANGE
If Not IsEmpty(SH.Range("B2")) Then
Set FirstCell = SH.Range("B2")
Else
Set FirstCell = SH.Range("B2").End(xlDown)
End If
Set LastCell = SH.Cells(Rows.Count, "B").End(xlUp)
SH.Range(FirstCell, LastCell).EntireRow.Copy
destRng.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlPasteSpecialOperationNone, _
SkipBlanks:=False, _
Transpose:=False
Application.CutCopyMode = False
End Sub
'<<================
N
Norman Jones
Hi Kelli,
For addition clarification, you should change:
to the "somewhere else" range.
For addition clarification, you should change:
to the "somewhere else" range.
G
Guest
FABULOUS! Thanks again for all the help!!!
Norman Jones said:
G
Guest
Yes, thanks.. I ended up adapting my project and created a sheet 2 for the
dest. It will work better all around. I'm almost all done. I hate to ask
for more help, but if you're not totally sick of me yet...
Since I am going to keep the final product in Excel, I realized I can tailor
one more view, so I now need to delete all rows for which there are no
positive numbers in H, on sheet 2 only.
I tried several adaptations of the code you gave for deleting "populated B"
rows, but can't make it work. I may have the same problem with the initial
cut/pastevalue in that it is not recognizing the contents of the cells as
numbers?
This is what I tried... I get an error with the Union being a bad call:
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
Set SH = WB.Sheets("Floor")
Set rng = Intersect(SH.UsedRange, SH.Columns("H:H"))
For Each rCell In rng.Cells
If IsEmpty(rCell) Or rCell.Value = "" Then
If Not 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
End Sub
dest. It will work better all around. I'm almost all done. I hate to ask
for more help, but if you're not totally sick of me yet...
Since I am going to keep the final product in Excel, I realized I can tailor
one more view, so I now need to delete all rows for which there are no
positive numbers in H, on sheet 2 only.
I tried several adaptations of the code you gave for deleting "populated B"
rows, but can't make it work. I may have the same problem with the initial
cut/pastevalue in that it is not recognizing the contents of the cells as
numbers?
This is what I tried... I get an error with the Union being a bad call:
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
Set SH = WB.Sheets("Floor")
Set rng = Intersect(SH.UsedRange, SH.Columns("H:H"))
For Each rCell In rng.Cells
If IsEmpty(rCell) Or rCell.Value = "" Then
If Not 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
End Sub
Norman Jones said: