Macro to delete rows

[howard]

I have a macro where I wants to delete all rows containing a specified number for eg 10. However when running the macro no rows are being deleted

Your asistance in resolving this is most appreciated

 Sub Delete_PR_Period()

Sheets("Data").Select
Range("P1").Select
ActiveCell.FormulaR1C1 = InputBox("select prior period to delete for eg 9")
Application.ScreenUpdating = False


Static fx, WS1, Holder, Lr, LrRange, Counter

Set WS1 = Sheets("Data")
WS1.Activate
Holder = WS1.Range("AA1").Value
Lr = WS1.Range("J100000").End(xlUp).Row
LrRange = WS1.Range("J2:J" & Lr).Address(False, False)
Counter = 0
While Counter < 6
Counter = Counter + 1
    For Each fx In WS1.Range(LrRange)
 
        If fx.Value > "" Then
        
                If fx.Value = Holder Then
        
                        fx.EntireRow.Select
                        Selection.Delete Shift:=xlUp
                
                End If
       
        End If
    Next fx
Wend

End Sub  [

 

[farmerscott]

Howard,

Is your code erroring at all??


your code has a few issues...

1. you use an input box to get a number(??). However, you are asking VBA to insert it into the cell as a formula.

2. there seems to be no-where else you use this data/information in the rest of your code.

3. fx should be declared as a range.

4. With

LrRange = WS1.Range("J2:J" & Lr).Address(False, False),

I am not sure of this line as I have never seen a range set this way. Further you need to use "Set LrRange= xxxx"

5. why do you have the counter set at a max of 5 (<6)? What is the purpose of the counter/counting??

6. if you are deleting rows then you have to work from the bottom of your range and work up.

7. with this line

If fx.Value > "" Then

, are you trying to ask if it is not blank? if so, use

If fx.Value <> "" then

hope this helps,

FarmerScott

 

[Rick Rothstein]

I have a macro where I wants to delete all rows containing a specified number for eg 10.

If the values in Column J are all constants (no formulas) and if either your version of Excel is 2010 (or greater) or, if you are using a lower version of Excel, if the number of values that will end up being found is less than 16000, then you should be able to use this much shorter macro...

Sub Delete_PR_Period()
  Columns("J").Replace Range("AA1").Value, "#N/A", xlWhole
  Columns("J").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End Sub

 

[howard]

Hi Guys

Thanks for the help much appeciated