Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows based on data NOT meeting criteria --working macro here, just need help with tweaking
Here is the model of the code I'm using -- this come from
http://www.rondebruin.nl/delete.htm Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView Firstrow = ActiveSheet.UsedRange.Cells(1).Row Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1 With ActiveSheet .DisplayPageBreaks = False For Lrow = Lastrow To Firstrow Step -1 If IsError(.Cells(Lrow, "A").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "A").Value = "ron" Then .Rows(Lrow).Delete 'This will delete each row with the Value "ron" in Column A, case sensitive. End If Next End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub __________________________________________________ __ Here is my situation and my questions: In Column A on my spreadsheet, there is data like this: AR3303 AR4055 Subtotal Blank Cell (and row) Blank Cell (and row) AR9999 DP3838 DP3923 DP3932 What I want to do is delete all rows that do NOT contain the DP in the first cell (Column A cell). I think I could do this somehow using a LEFT function and obviously an IF NOT function somehow, but I'm having problems tweaking it to do it this way. Can anybody help? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows based on data NOT meeting criteria --working macro h
Sub DelRw()
lstRw = Cells(Rows.Count, 1).End(xlUp).Row For i = lstRw To 2 Step -1 'Assumes Header Row x = Cells(i, 1).Value If Left(x, 2) < "DP" Then Cells(i, 1).EntireRow.Delete End If Next "Zarlot531" wrote: Here is the model of the code I'm using -- this come from http://www.rondebruin.nl/delete.htm Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView Firstrow = ActiveSheet.UsedRange.Cells(1).Row Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1 With ActiveSheet .DisplayPageBreaks = False For Lrow = Lastrow To Firstrow Step -1 If IsError(.Cells(Lrow, "A").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "A").Value = "ron" Then .Rows(Lrow).Delete 'This will delete each row with the Value "ron" in Column A, case sensitive. End If Next End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub __________________________________________________ __ Here is my situation and my questions: In Column A on my spreadsheet, there is data like this: AR3303 AR4055 Subtotal Blank Cell (and row) Blank Cell (and row) AR9999 DP3838 DP3923 DP3932 What I want to do is delete all rows that do NOT contain the DP in the first cell (Column A cell). I think I could do this somehow using a LEFT function and obviously an IF NOT function somehow, but I'm having problems tweaking it to do it this way. Can anybody help? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows based on data NOT meeting criteria --working macro h
Wow you're a genius ... thanks a lot!
On Apr 28, 3:16 pm, JLGWhiz wrote: Sub DelRw() lstRw = Cells(Rows.Count, 1).End(xlUp).Row For i = lstRw To 2 Step -1 'Assumes Header Row x = Cells(i, 1).Value If Left(x, 2) < "DP" Then Cells(i, 1).EntireRow.Delete End If Next "Zarlot531" wrote: Here is the model of the code I'm using -- this come from http://www.rondebruin.nl/delete.htm Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView Firstrow = ActiveSheet.UsedRange.Cells(1).Row Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1 With ActiveSheet .DisplayPageBreaks = False For Lrow = Lastrow To Firstrow Step -1 If IsError(.Cells(Lrow, "A").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "A").Value = "ron" Then .Rows(Lrow).Delete 'This will delete each row with the Value "ron" in Column A, case sensitive. End If Next End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub __________________________________________________ __ Here is my situation and my questions: In Column A on my spreadsheet, there is data like this: AR3303 AR4055 Subtotal Blank Cell (and row) Blank Cell (and row) AR9999 DP3838 DP3923 DP3932 What I want to do is delete all rows that do NOT contain the DP in the first cell (Column A cell). I think I could do this somehow using a LEFT function and obviously an IF NOT function somehow, but I'm having problems tweaking it to do it this way. Can anybody help?- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows based on data NOT meeting criteria --working macro h
Here is my preliminary code. I've changed it around a little from
what you've written, but could you explain what the "To 1 Step -1" really means? I changed it from 2 to 1 because I have no header, and I asusme the -1 is referring to the footer. But one problem I'm having is that I run the code and it works perfectly except for the fact that it stops reading too soon. For example, there will be a few more lines left (rows who don't have data starting for five or six columns over) at the very end. If I move the lines at the end over to the very first column, then it reads it and is fine, or if there happens to be a row that is over far left enough at the end, then everything is fine. Which part of this code controls how far over the program reads to tell when the rows have stopped? Thanks... Sub DelRw() Dim lstRw Dim i Dim x Dim CalcMode With Application CalcMode = .Calculation ..Calculation = xlCalculationManual ..ScreenUpdating = False End With lstRw = Cells(Rows.Count, 1).End(xlUp).Row For i = lstRw To 1 Step -1 x = Cells(i, 3).Value If Left(x, 4) < "2745" Then Cells(i, 3).EntireRow.Delete End If Next With Application ..ScreenUpdating = True ..Calculation = CalcMode End With End Sub On Apr 28, 3:16 pm, JLGWhiz wrote: Sub DelRw() lstRw = Cells(Rows.Count, 1).End(xlUp).Row For i = lstRw To 2 Step -1 'Assumes Header Row x = Cells(i, 1).Value If Left(x, 2) < "DP" Then Cells(i, 1).EntireRow.Delete End If Next "Zarlot531" wrote: Here is the model of the code I'm using -- this come from http://www.rondebruin.nl/delete.htm Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView Firstrow = ActiveSheet.UsedRange.Cells(1).Row Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1 With ActiveSheet .DisplayPageBreaks = False For Lrow = Lastrow To Firstrow Step -1 If IsError(.Cells(Lrow, "A").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "A").Value = "ron" Then .Rows(Lrow).Delete 'This will delete each row with the Value "ron" in Column A, case sensitive. End If Next End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub __________________________________________________ __ Here is my situation and my questions: In Column A on my spreadsheet, there is data like this: AR3303 AR4055 Subtotal Blank Cell (and row) Blank Cell (and row) AR9999 DP3838 DP3923 DP3932 What I want to do is delete all rows that do NOT contain the DP in the first cell (Column A cell). I think I could do this somehow using a LEFT function and obviously an IF NOT function somehow, but I'm having problems tweaking it to do it this way. Can anybody help?- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows based on data NOT meeting criteria --working mac
The step -1 is to walk from the bottom of the column to the top.
Since you are deleting rows, and the default is to shift up, it is better to start at the bottom and work toward the top. Otherwise it would skip rows as it worked its way down. The last row (lstRw) is calculated in the code you are using based on column "A". You can do one of two things: 1- Change lstRw = Cells(Rows.Count, ?).End(xlUp).Row to replace the question mark with whichever column you have the most data in or 2- Chage to lstRw = Sheets(1).UsedRange.SpecialCells(xlCellTypeLastCel l).Row Either of the two methods should then cover all of the rows with data in them. Since you are only searching a single column, this particular code does not take the length of the row into consideration except for the delete command where the entire row is deleted. "Zarlot531" wrote: Here is my preliminary code. I've changed it around a little from what you've written, but could you explain what the "To 1 Step -1" really means? I changed it from 2 to 1 because I have no header, and I asusme the -1 is referring to the footer. But one problem I'm having is that I run the code and it works perfectly except for the fact that it stops reading too soon. For example, there will be a few more lines left (rows who don't have data starting for five or six columns over) at the very end. If I move the lines at the end over to the very first column, then it reads it and is fine, or if there happens to be a row that is over far left enough at the end, then everything is fine. Which part of this code controls how far over the program reads to tell when the rows have stopped? Thanks... Sub DelRw() Dim lstRw Dim i Dim x Dim CalcMode With Application CalcMode = .Calculation ..Calculation = xlCalculationManual ..ScreenUpdating = False End With lstRw = Cells(Rows.Count, 1).End(xlUp).Row For i = lstRw To 1 Step -1 x = Cells(i, 3).Value If Left(x, 4) < "2745" Then Cells(i, 3).EntireRow.Delete End If Next With Application ..ScreenUpdating = True ..Calculation = CalcMode End With End Sub On Apr 28, 3:16 pm, JLGWhiz wrote: Sub DelRw() lstRw = Cells(Rows.Count, 1).End(xlUp).Row For i = lstRw To 2 Step -1 'Assumes Header Row x = Cells(i, 1).Value If Left(x, 2) < "DP" Then Cells(i, 1).EntireRow.Delete End If Next "Zarlot531" wrote: Here is the model of the code I'm using -- this come from http://www.rondebruin.nl/delete.htm Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView Firstrow = ActiveSheet.UsedRange.Cells(1).Row Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1 With ActiveSheet .DisplayPageBreaks = False For Lrow = Lastrow To Firstrow Step -1 If IsError(.Cells(Lrow, "A").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "A").Value = "ron" Then .Rows(Lrow).Delete 'This will delete each row with the Value "ron" in Column A, case sensitive. End If Next End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub __________________________________________________ __ Here is my situation and my questions: In Column A on my spreadsheet, there is data like this: AR3303 AR4055 Subtotal Blank Cell (and row) Blank Cell (and row) AR9999 DP3838 DP3923 DP3932 What I want to do is delete all rows that do NOT contain the DP in the first cell (Column A cell). I think I could do this somehow using a LEFT function and obviously an IF NOT function somehow, but I'm having problems tweaking it to do it this way. Can anybody help?- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting rows meeting certain criteria in a particular column | Excel Discussion (Misc queries) | |||
Deleting entire rows based on certain criteria | Excel Programming | |||
Deleting rows based on multiple criteria | Excel Programming | |||
Deleting rows based on cell criteria | Excel Programming | |||
Deleting rows based on criteria | Excel Programming |