delete rows which have a one in them

[kyle]

i've got this code built that will delete a row if there is a one in column h
but it's not working

sub delete()

finalrow = celss(rows.count, 1).end(xlup).row

for i = finalrow to 2 step -1
if cells(i, 8) = 1 then
cells(i, 1).entirerow.delete
endif
next i
end sub

it doesn't work, what do you think is wrong?

 

[Dennis Tucker]

Try this.

Sub Macro1()

' count down through the rows(from bottom to the top)
For MyRow = Worksheets("Sheet1").UsedRange.Rows.Count To 2 Step -1
' check column H for a "1"
If Worksheets("Sheet1").Range("H" + CStr(MyRow)).Value = "1" Then
' select the row
Rows(CStr(MyRow) + ":" + CStr(MyRow)).Select
' delete the row & shift data up
Selection.Delete Shift:=xlUp
End If
Next MyRow

End Sub

 

[Don Guillett]

finalrow = celss(rows.count, 1).end(xlup).row

finalrow = cells(rows.count, 1).end(xlup).row

endif

end if


sub delete()
for i = cells(rows.count, 1).end(xlup).row to 2 step-1
if cells(i, 8) = 1 then rows(i).delete
next i
end sub

 

[kyle]

dennis

thanks for trying to help me out. when i use your code after i write

("h" + cstr(my row))

it says compile error: expected: list separator or )

i eventually got the code to work with

for i = 1 to 67000
if cells(i, 8).value = 1 then
cells(i, 1).entirerow.delete
end if
next i

but i don't see why

finalrow = cells(rows.count, 1).end(xlup).row

won't work, seeing as i got it from mrecxel himself, bill jelen

 

[kyle]

Don,

the final row still isn't working, i've uploaded a picture to show you what
i mean

http://i87.photobucket.com/albums/k137/kylefoley76/Picture3-10.png

 

[Kurt Barr]

You are getting the compile error because you've named your macro the same
thing as you named the variable. Change one or the other, and it should work.

 

[Ryan H]

There are a few things wrong with your code.

1.) You named you procedure delete(). Delete is a method in VBA and to
avoid any errors or confusion your should name your subs something
meaningful, like DeleteRows()

2.) You have undeclared variables. This can be a problem when trying to
debug code. You should make it a good practice of declaring your variables.

Dim FinalRow As Long
Dim i As Long

3.) I would recommend writting Cells(i, 8) like Cells(i, "H"). This will
make your code easier to read. For example, what column number is
"T"..........are you still trying to figure it out..........lol. It's easier
to use this Cells(i, 8). By the way the answer is 20.

4.) Plus, you missed spelled Cells in "finalrow = celss(rows.count,
1).end(xlup).row". It should be FinalRow = Cells(Rows.Count,
"A").End(xlUp).Row

5.) I see you are trying to use a Sub named finalrow(). No need to use
that. Delete that bit of code and use this.

Sub DeleteRows()

Dim FinalRow As Long
Dim i As Long

FinalRow = Cells(Rows.Count, "A").End(xlUp).Row

For i = FinalRow To 2 Step -1
If Cells(i, "H") = 1 Then
Rows(i).EntireRow.delete
End If
Next i

End Sub

Sorry to go on and on just trying to help. Hope this helps! If so, let me
know, click "YES" below.

 

[Jef Gorbach]

You misspelled "celss" when assigning finalrow.


It would probably be faster to filter the range for rows where
column(H)=1 then delete the results.

Sub FilterDelete()
Dim FilterRange As Range
finalrow = Cells(Rows.Count, 1).End(xlUp).Row
Set FilterRange = Range("A1:H" & finalrow)
FilterRange.AutoFilter Field:=8, Criteria1:=1
FilterRange.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End Sub