Need Macro for deleting rows

Microsoft Excel 2003 (full product)
July 14, 2010 at 05:56:16
Specs: Windows XP

I need urgent assistance for the following.

I want to delete entire rows from a spreadsheet when cells Gx = Fx

e.g. when the value G6 = 20 matches value in F6 = 20 than the entire row needs to be deleted

I am aware this is easily to be done manually but it is a huge data dump which is run multiple times a day my clients prefers automating this by creating a macro.

Hope this is clear enough.

thanks in advance for your help!

See More: Need Macro for deleting rows

Report •

July 14, 2010 at 07:18:01
This is the current set up

ActiveCell.FormulaR1C1 = "=RC[-9]-RC[-8]"
Selection.AutoFill Destination:=Range("O2:O2500")

Now i want to include in my macro the option to delete any row where the value of the

ActiveCell.FormulaR1C1 = "=RC[-9]-RC[-8]" equals to 0


Report •

July 14, 2010 at 08:13:15
Let's start with the fact that your current macro can be reduced to a single line:

Range("O2:O2500").FormulaR1C1 = "=RC[-9]-RC[-8]"

After the formulas are in the cells, we then loop through the range from the bottom up to avoid screwing up the For-Next Loop:

Option Explicit
Sub FillThenDelete()
Dim nxtRow As Integer
'Turn off screen flickering
 Application.ScreenUpdating = False
'Populate Column O Range with Formula
  Range("O2:O2500").FormulaR1C1 = "=RC[-9]-RC[-8]"
'Loop through Column O Range, deleting Rows where Ox = 0
  For nxtRow = 2500 To 2 Step -1
   If Range("O" & nxtRow) = 0 Then _
      Range("O" & nxtRow).EntireRow.Delete
  Application.ScreenUpdating = False
 End Sub

Report •

July 15, 2010 at 00:22:17
Hi DerbyDad,

Tried it immediately and it works! Great!

thanks a lot for your help!


Report •
Related Solutions

Ask Question