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?

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


"kyle" <(E-Mail Removed)> wrote in message
news:E5475A1B-E367-40DF-B114-(E-Mail Removed)...
> 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?

>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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"kyle" <(E-Mail Removed)> wrote in message
news:E5475A1B-E367-40DF-B114-(E-Mail Removed)...
> 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

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

"Dennis Tucker" wrote:

> 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
>
>
> "kyle" <(E-Mail Removed)> wrote in message
> news:E5475A1B-E367-40DF-B114-(E-Mail Removed)...
> > 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?
>
> .
>

Don,

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

http://i87.photobucket.com/albums/k1...icture3-10.png

"Don Guillett" wrote:

>
> >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
>
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "kyle" <(E-Mail Removed)> wrote in message
> news:E5475A1B-E367-40DF-B114-(E-Mail Removed)...
> > 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?
>
> .
>

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.

"kyle" wrote:

> Don,
>
> the final row still isn't working, i've uploaded a picture to show you what
> i mean
>
> http://i87.photobucket.com/albums/k1...icture3-10.png
>
> "Don Guillett" wrote:
>
> >
> > >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
> >
> >
> > --
> > Don Guillett
> > Microsoft MVP Excel
> > SalesAid Software
> > (E-Mail Removed)
> > "kyle" <(E-Mail Removed)> wrote in message
> > news:E5475A1B-E367-40DF-B114-(E-Mail Removed)...
> > > 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?
> >
> > .
> >

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.
--
Cheers,
Ryan


"kyle" wrote:

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

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