PC Review


Reply
Thread Tools Rate Thread

delete rows which have a one in them

 
 
kyle
Guest
Posts: n/a
 
      17th Mar 2010
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?
 
Reply With Quote
 
 
 
 
Dennis Tucker
Guest
Posts: n/a
 
      17th Mar 2010
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?


 
Reply With Quote
 
Don Guillett
Guest
Posts: n/a
 
      17th Mar 2010

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


 
Reply With Quote
 
kyle
Guest
Posts: n/a
 
      17th Mar 2010
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?

>
> .
>

 
Reply With Quote
 
kyle
Guest
Posts: n/a
 
      17th Mar 2010
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?

>
> .
>

 
Reply With Quote
 
Kurt Barr
Guest
Posts: n/a
 
      17th Mar 2010
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?

> >
> > .
> >

 
Reply With Quote
 
Ryan H
Guest
Posts: n/a
 
      17th Mar 2010
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?

 
Reply With Quote
 
Jef Gorbach
Guest
Posts: n/a
 
      17th Mar 2010
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
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Autofil on variable rows, delete extract and show remaining rows 1plane Microsoft Excel Programming 3 17th Nov 2009 10:49 AM
Hpw do I delete multiple empty rows found between filled rows? Bill Microsoft Excel Worksheet Functions 1 15th Nov 2009 12:52 AM
Copy pasting Rows, but need to Delete any Shapes/Pictures that are within copied rows Corey Microsoft Excel Programming 2 1st Aug 2007 02:02 AM
Cut filtered rows, paste into next empty row of new sheet, and delete cut rows Scott Microsoft Excel Worksheet Functions 0 13th Dec 2006 01:25 AM
Delete rows with numeric values, leave rows with text =?Utf-8?B?R1NwbGluZQ==?= Microsoft Excel Programming 5 11th Oct 2005 12:44 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 07:13 PM.