Macro Not Working

[Guest]

I'm trying to create a loop macro that will delete a row based on its value,
but somehow is not working. I'm not an expert in macros so can anyone point
me in the right direction?

Please HELP!

Sub rowdeletion()

' rowdeletion Macro

x = ActiveCell.Row

Do While Cells(x, 2).Value <> "" (I keep getting a type missmatch error on
this line)



'If (Columns("B:B").Select <> "#VALUE!") Then

' Rows("1:1").Select

' Selection.Delete Shift:=xlUp

' End If



x = x + 1

Loop

End Sub


Thanks

 

[Guest]

if you want do delete rows with a blank value in column 2

sub DeleteRows()
on Error Resume Next
columns(2).specialCells(xlBlanks).EntireRow.delete
On Error goto 0
End sub

 

[Guest]

Thanks for the quick response Tom. I have two columns and column B is the
column I want to test for. Some of the cells in that column contains numbers
and the other cells are set to (#value) I want my macro to delete the rows
where the cell content is an actual value and i want to leave the ones that
are set to #value.

Where in the macro i created would i add the code you gave me? Thanks again
for your help.

 

[Guest]

you have a couple of choices

sub DeleteRows()
on Error Resume Next
columns(2).specialCells(xlBlanks).EntireRow.delete
columns(2).SpecialCells(xlformulas,xlNumbers)
columns(2).specialCells(xlformulas,xlTextValues)
columns(2).SpecialCells(xlConstants,xlNumbers)
columns(2).SpecialClels(xlConstants,xlTextValues)
On Error goto 0
End sub

remove the ones you don't want. There is also an xlErrors, but you said you
wanted to keep those.

 

[Guest]

Tom,

should i incorporate the code you gave me to the macro I was trying to
create or should i just use the code you suggested alone. I'm not sure how
to proceed with what you have suggested.

Thanks again for the quick response.

 

[Tom Ogilvy]

Select column B

then do
Edit=>Goto=.Special

Select Constants or Formulas and the options you want below that.

then click OK

The next Step would be to do Edit=>Delete and select entirerow.

This is what the code does. You don't need anything else. Decide which
line (lines) of code you need.