Help with Visual Basic for Excel
 

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

Help with Visual Basic for Excel
 

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

Help with Visual Basic for Excel
 

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

Help with Visual Basic for Excel
 

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

Help with Visual Basic for Excel
 

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

Help with Visual Basic for Excel
 

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.

Help with Visual Basic for Excel
 

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

"Norman Jones" wrote:

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.

Help with Visual Basic for Excel
 

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...


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

Help with Visual Basic for Excel
 

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

Help with Visual Basic for Excel
 

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!

"broro183" wrote:


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...


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

Help with Visual Basic for Excel
 

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: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
'<<================


---
Regards,
Norman


"KelliInCali" wrote in message
...
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!

Help with Visual Basic for Excel
 

ThankyouThankyouThankyou Norman! After a small addition to save my header
row, this works perf! Have a great day! -kelli

"Norman Jones" wrote:

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: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
'<<================


---
Regards,
Norman


"KelliInCali" wrote in message
...
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!

Help with Visual Basic for Excel
 

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


"Norman Jones" wrote:

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: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
'<<================


---
Regards,
Norman


"KelliInCali" wrote in message
...
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!

Help with Visual Basic for Excel
 

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.


---
Regards,
Norman



"KelliInCali" wrote in message
...
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

Help with Visual Basic for Excel
 

Hi Kelli,

Change:

SH.Range(FirstCell, LastCell).Copy Destination:=destRng


to

SH.Range(FirstCell, LastCell).EntireRow.Copy _
Destination:=destRng

---
Regards,
Norman

Help with Visual Basic for Excel
 

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


"Norman Jones" wrote:

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.


---
Regards,
Norman



"KelliInCali" wrote in message
...
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

Help with Visual Basic for Excel
 

Hi Kelli,

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

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
'<<================


---
Regards,
Norman

Help with Visual Basic for Excel
 

Hi Kelli,

For addition clarification, you should change:

Set destRng = WB.Sheets("Sheet2").Range("A1")

to the "somewhere else" range.


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Kelli,

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

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
'<<================


---
Regards,
Norman

Help with Visual Basic for Excel
 

FABULOUS! Thanks again for all the help!!!


"Norman Jones" wrote:

Hi Kelli,

Change:

SH.Range(FirstCell, LastCell).Copy Destination:=destRng


to

SH.Range(FirstCell, LastCell).EntireRow.Copy _
Destination:=destRng

---
Regards,
Norman

Help with Visual Basic for Excel
 

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



"Norman Jones" wrote:

Hi Kelli,

For addition clarification, you should change:

Set destRng = WB.Sheets("Sheet2").Range("A1")

to the "somewhere else" range.


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Kelli,

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

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
'<<================


---
Regards,
Norman

Help with Visual Basic for Excel
 

Hi Kelli,

Your error occurs because of an extraneous 'Not'. The Not reverses the logic
and, consequently, you attempt to form a union with a non-existant range,
which is not possible.

Therefore, change:

If Not delRng Is Nothing Then

to

If delRng Is Nothing Then


As a separate issue, and depending on your definition of:

positive numbers in H

you may wish to change the condition line:

If IsEmpty(rCell) Or rCell.Value = "" Then

to

If RCell.Value <= 0 Then


---
Regards,
Norman


"KelliInCali" wrote in message
...
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

Help with Visual Basic for Excel
 

Thanks Norman! I don't get the bad call error any more, but it isn't
recognizing the values in H either. I added the <= 0 as an Or statement to
the condition line, so now it should be finding anything that is <= 0, empty,
or blank text, but it's not removing any lines. In the test file I'm working
with, there should only be two records remaining after running.

"Norman Jones" wrote:

Hi Kelli,

Your error occurs because of an extraneous 'Not'. The Not reverses the logic
and, consequently, you attempt to form a union with a non-existant range,
which is not possible.

Therefore, change:

If Not delRng Is Nothing Then

to

If delRng Is Nothing Then


As a separate issue, and depending on your definition of:

positive numbers in H

you may wish to change the condition line:

If IsEmpty(rCell) Or rCell.Value = "" Then

to

If RCell.Value <= 0 Then


---
Regards,
Norman


"KelliInCali" wrote in message
...
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

Help with Visual Basic for Excel
 

Can you send me this. I have to do the same thing except cut it into another
worksheet tab.
Thanks Joe

KelliInCali wrote:
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

Hi Kelli,

[quoted text clipped - 60 lines]
empties the non-empty empty cells? Does knowtrumps code do that?
-Tks guys!

Help with Visual Basic for Excel
 

Joe,

I'm not sure which function you are after exactly. Here is my entire
macro.. It may not be the tidiest code in the world, but it's doing the job..
Hope it helps you -kelli


Sub CullSort()
'

'Copies and Pastes values for entire sheet to eliminate formulas
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False




'Sorts by "Master" (Column P) so that following removal sequence runs faster
Cells.Select
Selection.Sort Key1:=Range("P2"), Order1:=xlDescending, Header:=xlGuess,
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal




'Removes entire row for unwanted records based on calculated formulas in
Column P
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("Prelim")
Set rng = Intersect(SH.UsedRange, SH.Columns("P:P"))

On Error GoTo XIT
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

For Each rCell In rng.Cells
If rCell.Value = "DELETE" 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



' Blanks out zero quantities in all Qty columns (note to self: there is
probably a faster way to do this)
Dim list As Integer
For list = 1 To 20000
If Cells(list, 8) = "0" Then
Cells(list, 8) = ""
End If
If Cells(list, 9) = "0" Then
Cells(list, 9) = ""
End If
If Cells(list, 11) = "0" Then
Cells(list, 11) = ""
End If
If Cells(list, 12) = "0" Then
Cells(list, 12) = ""
End If
Next
'


'Removes now unnecessary formula columns and pastes some results over
original data
Columns("T:T").Select
Selection.Cut
Columns("N:N").Select
ActiveSheet.Paste
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Columns("O:R").Select
Selection.Delete Shift:=xlToLeft

Range("A1").Select




'Copies all rows where B is not empty and pastes values in pre-existing sheet2
Dim FirstCell As Range
Dim LastCell As Range
Dim destRng As Range

Set destRng = WB.Sheets("Buyer").Range("A2")
If Not IsEmpty(SH.Range("B2")) Then
Set FirstCell = SH.Range("A2")
Else
Set FirstCell = SH.Range("A2").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


'Formats and sorts "Buyer" sheet
Sheets("Buyer").Select
Cells.Select
Selection.Columns.AutoFit
Set tbl = ActiveCell.CurrentRegion
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count).Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Key2:=Range("C2"), Order2:=xlAscending, Key3:=Range("D2"),
Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("A2").Select


'Duplicates "Buyer" data in "Floor" sheet
Sheets("Buyer").Select
Set destRng = WB.Sheets("Floor").Range("A1")
Cells.Select
Selection.Copy
destRng.PasteSpecial xlPasteAll, xlPasteSpecialOperationNone,
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False


'Deletes entire row in "Floor" for records when H is blank or < 0
Dim ViewMode As Long
Dim rngCurrentCell As Range
Dim rowDel As Range

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

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView


Sheets("Floor").Select
Range("A2").Select

With ActiveSheet
.DisplayPageBreaks = False

Set tbl = ActiveCell.CurrentRegion
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count).Select


For Each rngCurrentCell In Worksheets("Floor").Range("H:H").Cells
If IsEmpty(rngCurrentCell) Or rngCurrentCell.Value < 0.01 Or
rngCurrentCell.Value = "" Then
If rowDel Is Nothing Then
Set rowDel = rngCurrentCell
Else
Set rowDel = Application.Union(rowDel, rngCurrentCell)
End If
End If

Next
End With

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

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


Range("A2").Select


Sheets("Buyer").Select
Range("A2").Select

Sheets("Prelim").Delete

End Sub


"jkrist46" wrote:

Can you send me this. I have to do the same thing except cut it into another
worksheet tab.
Thanks Joe

KelliInCali wrote:
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

Hi Kelli,

[quoted text clipped - 60 lines]
empties the non-empty empty cells? Does knowtrumps code do that?
-Tks guys!