Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Row
Hi, the code below is what i found from searching and it was from Bob, I
need to convert it to find the word "Total" and delete that row with total in it, But i am not too sure how to modify it ? Could somebody please help? Dim i As Long Dim iLastRow As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = iLastRow To 1 Step -1 If Cells(i, "A").Value < 6500 And Cells(i, "A").Value 5599 Then Rows(i).Delete End If Next i Tempy *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Row
hi,
If you word "total is all in the same column then you can use this code. otherwise you might have a problem. it could be used but you may have to run it for each column the word "total appears. in this line, iLastRow = Cells(Rows.Count, "A").End(xlUp).Row the letter "A" denote column A. the letter i denotes the row number. change the "A" to the column that the word "total" apears in. change this line... If Cells(i, "A").Value < 6500 And Cells(i, "A").Value 5599 Then to this If Cells(1,"yourcolumn").value = "Totals" then if that doesn't work for you, try something like this... Sub mac1FindAll() Dim c As String Dim sh As Worksheet Dim rng As Range Dim cAddr As String 'c = InputBox("Enter item to delete") 'Optional method c="Totals" For Each sh In ActiveWorkbook.Worksheets If c < "" Then Set rng = Nothing Set rng = sh.Range("A1:IV65000").Find(what:=c, _ After:=sh.Range("A1"), _ LookIn:=xlFormulas, _ lookat:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End If If Not rng Is Nothing Then cAddr = rng.Address sh.Activate rng.Select Selection.entirerow.delete shift:=xlup Do Set rng = Range("a1:IV65000").FindNext(rng) If rng.Address = cAddr Then MsgBox ("no more " & c & " 's were found.") Exit Do Else rng.Select Selection.entirerow.delete shift:=xlup End If Loop Until rng.Address = cAddr End If Next sh If rng Is Nothing Then MsgBox c & " was Not found" End If End Sub regards FSt1 "Tempy" wrote: Hi, the code below is what i found from searching and it was from Bob, I need to convert it to find the word "Total" and delete that row with total in it, But i am not too sure how to modify it ? Could somebody please help? Dim i As Long Dim iLastRow As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = iLastRow To 1 Step -1 If Cells(i, "A").Value < 6500 And Cells(i, "A").Value 5599 Then Rows(i).Delete End If Next i Tempy *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Row
This should do it. Post back if you need more. HTH Otto
Sub DeleteTotal() Dim i As Long Dim iLastRow As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = iLastRow To 1 Step -1 If Cells(i, "A").Value = "Total" Then Rows(i).Delete End If Next i End Sub "Tempy" wrote in message ... Hi, the code below is what i found from searching and it was from Bob, I need to convert it to find the word "Total" and delete that row with total in it, But i am not too sure how to modify it ? Could somebody please help? Dim i As Long Dim iLastRow As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = iLastRow To 1 Step -1 If Cells(i, "A").Value < 6500 And Cells(i, "A").Value 5599 Then Rows(i).Delete End If Next i Tempy *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Row
Dim i As Long
Dim iLastRow As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = iLastRow To 1 Step -1 If lcase(Cells(i, "A").Value) = "total" Then Rows(i).Delete End If Next i if the cell will contain more than the word total, but you want to delete it if it does contain the word total as part of a larger string: (it will work if total is in the cell by itself as well) Dim i As Long Dim iLastRow As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = iLastRow To 1 Step -1 If Instr(1,Cells(i, "A").Value),"total",vbTextCompare) Then Rows(i).Delete End If Next i -- Regards, Tom Ogilvy "Tempy" wrote in message ... Hi, the code below is what i found from searching and it was from Bob, I need to convert it to find the word "Total" and delete that row with total in it, But i am not too sure how to modify it ? Could somebody please help? Dim i As Long Dim iLastRow As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = iLastRow To 1 Step -1 If Cells(i, "A").Value < 6500 And Cells(i, "A").Value 5599 Then Rows(i).Delete End If Next i Tempy *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Row
FSt1,
Thank you so much for your answer and explanation, it helps newbies like me to undertstand better Tempy *** Sent via Developersdex http://www.developersdex.com *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Row
Thanks Tom, that helps me a lot. I really want to compliment all that
help for a really great job, thank you. Tempy *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
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 |