Macro to Delete a Row

[Ty]

I posted a problem under "Excel Delete VB Macro" in the microsoft.­
public.­excel.­worksheet.­functions. And another one called VLOOKUP
in the last 5 days. All of the problems were resolved. i have 3 more
questions.

What if I just wanted to delete rows that have the cell blank?

my answer: ActiveCell.Interior.Color = RGB(255, 0, 0) need to
change
the RGB to the empty cell numbers(0, 0, 0)

What if I just wanted to delete rows that have #N/A in the cell
blank?

my answer: ActiveCell = #N/A

What if I had another column such as column J as a condition to the
above to not delete with my initials like TB?

my answer: If ActiveCell.Interior.Color = RGB(255, 0, 0) + column J
is
blank. Don't really have answer but I'm trying to figure this out on
my own.


Thanks for your help....
Ty

 

[Ty]

I posted a problem under "Excel Delete VB Macro" in the microsoft.­
public.­excel.­worksheet.­functions.  And another one called VLOOKUP
in the last 5 days.  All of the problems were resolved.  i have 3 more
questions.

What if  I just wanted to delete rows that have the cell blank?

my answer:  ActiveCell.Interior.Color = RGB(255, 0, 0)  need to
change
the RGB to the empty cell numbers(0, 0, 0)

What if  I just wanted to delete rows that have #N/A in the cell
blank?

my answer:  ActiveCell = #N/A

What if I had another column such as column J as a condition to the
above to not delete with my initials like TB?

my answer: If ActiveCell.Interior.Color = RGB(255, 0, 0) + column J
is
blank.  Don't really have answer but I'm trying to figure this out on
my own.

Thanks for your help....
Ty

I attempted to do one of my answers up top and if failed with an
error. Changed it to ActiveCell.Value = "#N/A". Now, I have an error
and the Do While line is yellow. The "active cell error 2042"
displays when I put my cursor over the [Do While ActiveCell <> ""]
line. Here is the complete Macro:

Sub MacroTy()
'
' MacroTy Macro
' Macro recorded 7/30/2009 by Ty
'

'
ScreenUpdating = False
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1

Do While ActiveCell <> ""
If ActiveCell.Value = "#N/A" Then
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
ScreenUpdating = True
End Sub

Help?

Thanks...

 

[Dave Peterson]

Without knowing anything about the code you're using...

#1. if isempty(activecell.value) then
#2. if lcase(activecell.text) = lcase("#N/A") then
#3. if ActiveCell.Interior.Color = RGB(255, 0, 0) _
and isempty(cells(activecell.row,"J").value) then

I hope that your delete code is working from the bottom up--it really makes
deleting much easier.

Personally, I wouldn't loop through the range using the activecell. I'd just
start a bottom row and work my way up.

Dim FirstRow as long
dim LastRow as long
dim iRow as long

with activesheet
firstrow = 2 'headers in row 1????
'last row determined by the data in column A (in my example)
lastrow = .cells(.rows.count,"A").end(xlup).row

for irow = lastrow to firstrow step -1
'what column should be tested? I used X.
if isempty(.cells(irow,"X").value) then
.rows(irow).delete
end if
next irow
end with

======
All untested, uncompiled. Watch for typos.

 

[Rick Rothstein]

Assuming there will not be thousands of rows whose formulas equate to the
#NA error, here is how I would write that function...

Sub RemoveRowsWithNA()
Dim C As Range, U As Range
For Each C In Columns("K").SpecialCells(xlCellTypeFormulas, xlErrors)
If C.Value = CVErr(xlErrNA) Then
If U Is Nothing Then
Set U = C
Else
Set U = Union(U, C)
End If
End If
Next
If Not U Is Nothing Then U.EntireRow.Delete
End Sub

Note that I have set the column in the For Each statement rather than use
the ActiveCell... just change my example Column "K" designation to the
column you actually want to search for the #NA errors in.

--
Rick (MVP - Excel)

I posted a problem under "Excel Delete VB Macro" in the microsoft.­
public.­excel.­worksheet.­functions. And another one called VLOOKUP
in the last 5 days. All of the problems were resolved. i have 3 more
questions.

What if I just wanted to delete rows that have the cell blank?

my answer: ActiveCell.Interior.Color = RGB(255, 0, 0) need to
change
the RGB to the empty cell numbers(0, 0, 0)

What if I just wanted to delete rows that have #N/A in the cell
blank?

my answer: ActiveCell = #N/A

What if I had another column such as column J as a condition to the
above to not delete with my initials like TB?

my answer: If ActiveCell.Interior.Color = RGB(255, 0, 0) + column J
is
blank. Don't really have answer but I'm trying to figure this out on
my own.

Thanks for your help....
Ty

I attempted to do one of my answers up top and if failed with an
error. Changed it to ActiveCell.Value = "#N/A". Now, I have an error
and the Do While line is yellow. The "active cell error 2042"
displays when I put my cursor over the [Do While ActiveCell <> ""]
line. Here is the complete Macro:

Sub MacroTy()
'
' MacroTy Macro
' Macro recorded 7/30/2009 by Ty
'

'
ScreenUpdating = False
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1

Do While ActiveCell <> ""
If ActiveCell.Value = "#N/A" Then
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
ScreenUpdating = True
End Sub

Help?

Thanks...

 

[Dave Peterson]

Check your other post.

I posted a problem under "Excel Delete VB Macro" in the microsoft.­
public.­excel.­worksheet.­functions. And another one called VLOOKUP
in the last 5 days. All of the problems were resolved. i have 3 more
questions.

What if I just wanted to delete rows that have the cell blank?

my answer: ActiveCell.Interior.Color = RGB(255, 0, 0) need to
change
the RGB to the empty cell numbers(0, 0, 0)

What if I just wanted to delete rows that have #N/A in the cell
blank?

my answer: ActiveCell = #N/A

What if I had another column such as column J as a condition to the
above to not delete with my initials like TB?

my answer: If ActiveCell.Interior.Color = RGB(255, 0, 0) + column J
is
blank. Don't really have answer but I'm trying to figure this out on
my own.

Thanks for your help....
Ty

 

[Dave Peterson]

Oops. Ignore this message.

Check your other post.