Delete rows (again)

[JEHalm]

Hello, I'm trying to edit this code that you had previously sent to me...which worked perfectly. However my supervisor asked to delete rows that are <1500 and > 1599.


Dim y As Long

For y = Range("L1:L" & Range("L" & Rows.Count).End(xlUp).Row).Rows.Count To 2 Step -1

If Cells(y, 12).Value < 1500 > 1599 Then Cells(y, 1).EntireRow.Delete Shift:=xlUp

Next y


I have tried several edits to no avail. I even tried the following:


Dim y As Long

For y = Range("L1:L" & Range("L" & Rows.Count).End(xlUp).Row).Rows.Count To 2 Step -1

If Cells(y, 12).Value < 1500 Then Cells(y, 1).EntireRow.Delete Shift:=xlUp

Next y


along with


Dim x As Long

For x = Range("L1:L" & Range("L" & Rows.Count).End(xlUp).Row).Rows.Count To 2 Step -1

If Cells(x, 12).Value > 1599 Then Cells(x, 1).EntireRow.Delete Shift:=xlUp

Next x


This last double entry worked with the exception that Row 2 was evaluated for some reason. Row 1 has all Header Titles


Using the double code (dim y as long) in the first for < 1500 and (dim x as long) for <1599.


Before After

L L

1650 1650 - wrong, > 1500 row should deleted

1535 1535 - correct

1755 1755 - correct

1352 1352 - correct

1599 1599 - correct


Finally, can you explain the code step by step. I'd like to see why the first row is deleted. Thanks in advance

 

[Hui]

JEHalm


The format of your formulas should be

If Cells(y, 12).Value < 1500 or Cells(y, 12).Value > 1599 Then Cells(y, 1).EntireRow.Delete Shift:=xlUp

Same for the others

 

[JEHalm]

Is there anyway to use the first two digits instead of all four? I tried this over the past weekend.


Dim y As Long

For y = Range("L1:L" & Range("L" & Rows.Count).End(xlUp).Row).Rows.Count To 2 Step -1

If Cells(y, 12).Value < > 15## Then Cells(y, 1).EntireRow.Delete Shift:=xlUp

Next y


I'm hoped that this would delete all rows from 1500 to 1599.


Also, what was I doing incorrectly when the row 2 wasn't deleted? possibly not being evaluated??


Lastly, how long is the open period for signing up for the PM tutorials?

 

[Luke M]

JEHalm,

Your macro doesn't know that 15## stands for, in this context. See macro below, I tried to explain it out pretty thoroughly.

[pre]

Sub DeleteRows()
Dim xRow As Integer
Dim x As Integer
'First, we figure out what row the last cell with data is in
'This line says to start in col L, and goto to last row (counts all rows
'in the workbook, and uses that number. In 2003, this would be 65536
'It then used the End method in the up direction to find
'the first cell (again, moving up) with text
'We identify the Row that cell is, and store this value in variable xRow
xRow = Range("L" & Rows.Count).End(xlUp).Row

'We're now going to tell the macro to go through a loop, using a
'starting and stopping number. We'll use variable xRow as the start point, and
'I'm guessing you want to stop at row 1. Thus, the 2nd number is 1
'Since we're not using a default count like 1, 2, 3, etc.
'we have to tell it to step backwards by 1

For x = xRow To 2 Step -1
'The cells method needs 2 arguments, a row and a column
'The column arguement can use either a number, or a letter
'note that if using a letter you need to use quotation marks

'The left function lets us look at just the left 2 numbers
If Left(Cells(x, 12).Value, 2) <> 15 Then
'Starting with the cell object, we take the entire row
'delete it, and let the macro know that we want to delete
'upwards
Cells(x, 1).EntireRow.Delete Shift:=xlUp
End If
Next y
End Sub

[/pre]

 

[Hui]

Luke & Jehalm

The problem with this approach is that numbers 15, 150-159, 15000-15999 etc will be incorrectly saved

 

[Luke M]

Doh! Right as always, Hui. =)

 

[JEHalm]

OK, so is there a way to evaluate the first two numbers without the incorrect numbers being saved? Or should I go using the four digit string from Hui

 

[Luke M]

You could either use the method Hui's did of two comparisons using greater than or less than, or you might be able to use this comparison:

If Left(Cells(x, 12).Value, 2) <> 15 And Len(Cells(x, 12).Value) = 4 Then