Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows which have a one in them
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows which have a one in them
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" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows which have a one in them
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 "kyle" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows which have a one in them
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" wrote in message ... 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? . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows which have a one in them
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 "kyle" wrote in message ... 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? . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows which have a one in them
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 "kyle" wrote in message ... 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? . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows which have a one in them
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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows which have a one in them
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).Entire Row.Delete End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete & Merge Columns,Delete Rows with filter, etc | Excel Programming | |||
Delete Rows if any cell in Column H is blank but do not Delete Fir | Excel Programming | |||
Copy pasting Rows, but need to Delete any Shapes/Pictures that are within copied rows | Excel Programming | |||
How to delete rows when List toolbar's "delete" isnt highlighted? | Excel Worksheet Functions | |||
Delete every 3rd row, then delete rows 2-7, move info f/every 2nd row up one to the end and delete the row below | Excel Programming |