delete row if value is less than 1

[Brianfree]

Hi, i have this code which is part of my macro which is suppose to delete any rows that have a value less than 1; however i have to keep running the macro to delete all the rows as it keeps leaving some rows behind. any ideas why?

For X = 1 To Y

With Range("C" & X).Select
If Range("C" & X).Value <= 1 Then
Range("C" & X).EntireRow.Delete

End If

End With

regards,

bf

 

[cardiac]

Have you missed 'Next X' ?

For X = 1 To Y

With Range("C" & X).Select
If Range("C" & X).Value <= 1 Then
Range("C" & X).EntireRow.Delete

End If

End With
Next X

 

[Brianfree]

Thanks for replying, just tried that and it still doesn't work.

Have also tried using...

Range("C" & X).EntireRow.Delete shift:=xlShiftUp

and it still does not work. It deletes some rows, then i have to run the macro again, and again to delete other rows. Very strange...

regards,

bf

 

[Brianfree]

Here is my entire macro if it helps..

Sub ProcessData()
  

Dim myLastRow As Long
Dim myLastCol As Long
Dim wks As Worksheet
Dim dummyRng As Range


For Each wks In ActiveWorkbook.Worksheets
  With wks
    myLastRow = 0
    myLastCol = 0
    Set dummyRng = .UsedRange
    On Error Resume Next
    myLastRow = _
      .Cells.Find("*", after:=.Cells(1), _
        LookIn:=xlFormulas, lookat:=xlWhole, _
        searchdirection:=xlPrevious, _
        searchorder:=xlByRows).Row
    myLastCol = _
      .Cells.Find("*", after:=.Cells(1), _
        LookIn:=xlFormulas, lookat:=xlWhole, _
        searchdirection:=xlPrevious, _
        searchorder:=xlByColumns).Column
    On Error GoTo 0

    If myLastRow * myLastCol = 0 Then
        .Columns.Delete
    Else
        .Range(.Cells(myLastRow + 1, 1), _
          .Cells(.Rows.Count, 1)).EntireRow.Delete
        .Range(.Cells(1, myLastCol + 1), _
          .Cells(1, .Columns.Count)).EntireColumn.Delete
    End If
  End With
Next wks

'Stops the screen from flickering

Application.ScreenUpdating = False

' Removing borders, setting font.

'MsgBox "Removing borders, setting font."

    Cells.Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Selection.Font.Bold = False
    Selection.Font.Name = "Ariel"
    Selection.Font.Size = 8
    
        
' Set format of Column A to Text.

'MsgBox "Set format of Column A to Text."

    'Columns("A:A").Select
    Selection.NumberFormat = "@"


    Dim X           As Long
    Dim Y           As Long
    Dim intX        As Long
    
    Y = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
        
    For X = 1 To Y
        
        With Range("C" & X).Select
            If Range("C" & X).Value < 1 Then
              '  Range("C" & X).Value = "Not In Stock"
                Range("C" & X).EntireRow.Delete shift:=xlShiftUp
            End If
        
        End With
    Next
        
    For X = 1 To Y
        
        With Range("D" & X).Select
            If InStr(ActiveCell.Text, "Compressor") Then
                Range("E" & X).Value = "PC143"
                Range("D" & X).Interior.ColorIndex = 7
                Range("D" & X).Font.Bold = True
                 
            ElseIf InStr(ActiveCell.Text, "Electric") Then
                Range("E" & X).Value = "PC148"
                Range("D" & X).Interior.ColorIndex = 38
                Range("D" & X).Font.Bold = True
                
            ElseIf InStr(ActiveCell.Text, "Electrics") Then
                Range("E" & X).Value = "PC148"
                Range("D" & X).Interior.ColorIndex = 38
                Range("D" & X).Font.Bold = True
                           
            ElseIf InStr(ActiveCell.Text, "Tacho") Then
                Range("E" & X).Value = "PC143"
                Range("D" & X).Interior.ColorIndex = 10
                Range("D" & X).Font.Bold = True
                                           
            ElseIf InStr(ActiveCell.Text, "ECU") Then
                Range("E" & X).Value = "PC149"
                Range("D" & X).Interior.ColorIndex = 35
                Range("D" & X).Font.Bold = True
                
            ElseIf InStr(ActiveCell.Text, "Braking") Then
                Range("E" & X).Value = "PC150"
                Range("D" & X).Interior.ColorIndex = 23
                Range("D" & X).Font.Bold = True
                              
            ElseIf InStr(ActiveCell.Text, "Other") Then
                Range("E" & X).Value = "PC133"
                Range("D" & X).Interior.ColorIndex = 24
                Range("D" & X).Font.Bold = True
                
           ' ElseIf InStr(ActiveCell.Text, "_NEW") Then
           '     Range("D" & X).Value = "_NEW"
           '     Range("D" & X).Interior.ColorIndex = 3
           '     Range("D" & X).Font.Bold = True
                
           ' ElseIf InStr(ActiveCell.Text, "_A") Then
           '    Range("D" & X).Value = "_AGRADE"
           '    Range("D" & X).Interior.ColorIndex = 4
           '    Range("D" & X).Font.Bold = True
           
        End If

        End With
    Next

' Sort Data by Flagged Column Desc then by OEM Asc.

'MsgBox "Sort Data by Flagged Column Desc then by OEM Asc."
    
    Columns("A:D").Select
    Selection.Sort Key1:=Range("D1"), Order1:=xlDescending, Key2:=Range("A1") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
    
    Range("A12").Select
End Sub

Regards,

bf

 

[combo]

Start at the bottom, non-evaluated rows will not be shifted up:

For X = Y To 1 Step -1
With Range("C" & X)
If .Value <= 1 Then
.EntireRow.Delete
End If
End With


combo

 

[cresbydotcom]

I was just going to say that deleting alters the actual row you are looking at not the value of x.

Another ruse is to subtract 1 from the current value of x if you delete, (but if row 1 needs deleting keep x at 1).

there is a tide in the affairs of man that you Cnut ignore.................