Remove data

[smartguy]

Hello all,

Good day.

In c drive i have 1 excel file.

Excel file Name : "acd.xls"

I Need to open and delete entire row.

Condtion : AA row contain "ZR" i need to delete entire row .

save the excel and close the excel.

Please help/.

 

[Fluff]

How about

Sub OpenDel()

   Dim wb As Workbook
   
   Set wb = Workbooks.Open("[COLOR=#ff0000]C:\MrExcel\acd.xls[/COLOR]")
   With wb.Sheets("Sheet1").Range("AA:AA")
      .Replace "*ZR*", "=XXX", xlPart, , False, , False, False
      .SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
   End With
   wb.Close True
   
End Sub

 

[smartguy]

How about

Sub OpenDel()

   Dim wb As Workbook
   
   Set wb = Workbooks.Open("[COLOR=#ff0000]C:\MrExcel\acd.xls[/COLOR]")
   With wb.Sheets("Sheet1").Range("AA:AA")
      .Replace "*ZR*", "=XXX", xlPart, , False, , False, False
      .SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
   End With
   wb.Close True
   
End Sub

Out of stack space error : 28...

Please help.

 

[Fluff]

How about this

Sub OpenDel()

   Dim wb As Workbook
   
   Set wb = Workbooks.Open("C:\MrExcel\acd.xls")
   With wb.Sheets("Sheet1")
      With .Range("AA1", .Range("AA" & Rows.Count).End(xlUp))
         .Replace "*ZR*", "=XXX", xlPart, , False, , False, False
         .SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
      End With
   End With
   wb.Close True
   
End Sub

 

[smartguy]

Hi Fluff,

thank you. it's working now.

I need 1 small change...

I need to delete previous Row also. ( Current row & Previous row ).

Please help

 

[Fluff]

Ok, try

Sub OpenDel()

   Dim wb As Workbook
   
   Set wb = Workbooks.Open("C:\MrExcel\acd.xls")
   With wb.Sheets("Sheet1")
      With .Range("AA1", .Range("AA" & Rows.Count).End(xlUp))
         .Replace "*ZR*", "=XXX", xlPart, , False, , False, False
         .SpecialCells(xlFormulas, xlErrors).Offset(-1).Value = "=XXX"
         .SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
      End With
   End With
   wb.Close True
   
End Sub

 

[smartguy]

Thanks

 

[Fluff]

Your welcome