Macro to delete rows

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,115
Office Version
  1. 2021
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  [
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

farmerscott

Well-known Member
Joined
Jan 26, 2013
Messages
814
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
37,866
Office Version
  1. 2019
  2. 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
6,115
Office Version
  1. 2021
Platform
  1. Windows
Hi Guys

Thanks for the help much appeciated
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,714
Messages
5,833,281
Members
430,201
Latest member
Deepakpilla36

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top