Macro to delete rows

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,633
Office Version
  1. 2019
Platform
  1. Windows
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

Code:
 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  [
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

farmerscott

Well-known Member
Joined
Jan 26, 2013
Messages
813
Office Version
  1. 365
Platform
  1. Windows
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
Code:
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
Code:
If fx.Value > "" Then
, are you trying to ask if it is not blank? if so, use
Code:
If fx.Value <> "" then
hope this helps,

FarmerScott
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,313
Office Version
  1. 2010
Platform
  1. Windows
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...
Code:
Sub Delete_PR_Period()
  Columns("J").Replace Range("AA1").Value, "#N/A", xlWhole
  Columns("J").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End Sub
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,633
Office Version
  1. 2019
Platform
  1. Windows
Hi Guys

Thanks for the help much appeciated
 

Watch MrExcel Video

Forum statistics

Threads
1,109,033
Messages
5,526,363
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top