0

I need help in achieving the below in my excel sheet with one master MACRO VBA Code

  1. If column A contains 0 - Delete the entire row. ( Row Killer ) - This should run on all the sheets in the workbook

  2. Since the excel file is heavily linked - All fields needs to converted to values (E.g. Paste as Values)

  3. Save AS file in .xls format with the file name SAMAmonthlyReport

I have the code for point 2 and 3 from this site but need help in adding the 1 point.

Below is the code

Sub CopyValuesToSync()
    Dim OrigWkbkFpth As String
    Dim OrigWkbk As String
    Dim ValueWkbk As String
    Dim WS As Worksheet

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    OrigWkbkFpth = Application.ActiveWorkbook.FullName
    OrigWkbk = Application.ActiveWorkbook.Name

    For Each WS In ActiveWorkbook.Worksheets
        WS.Select
        Cells.Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
        Range("A1").Select
    Next WS

    ActiveWorkbook.SaveAs Filename:="C:\SAMA\SamaMonthly.xls", _
    FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

    ValueWkbk = Application.ActiveWorkbook.Name

    Workbooks.Open Filename:=OrigWkbkFpth
    Windows(ValueWkbk).Activate
    ActiveWindow.Close
    Windows(OrigWkbk).Activate

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub
Community
  • 1
  • 1
Farhan
  • 13
  • 2
  • 2
    Possible duplicate of [Delete entire row if cell contains the string X](https://stackoverflow.com/questions/17606045/delete-entire-row-if-cell-contains-the-string-x) Just include, within your `For Each WS In ActiveWorkbook.Worksheets` loop, similar code to recommended in that question. – YowE3K Oct 18 '17 at 19:09
  • It is not working when I am inserting the code within the loop. – Farhan Oct 19 '17 at 15:59

1 Answers1

0

You can add a validation for each cell in column A using something like If cells(oRow,1).Value = 0 Then and then you can delete the Row with Rows(oRow).EntireRow.Delete

  • Can you guide me where do I need insert the line in the master code I mentioned ? I am new to VBA here – Farhan Oct 19 '17 at 16:00
  • You can insert your if block before you save the workbook. –  Oct 20 '17 at 15:17
  • I am getting the Run - time Error : 1004 I have inserted the below if block after "Next WS" line If Cells(oRow, 1).Value = 0 Then Rows(oRow).EntireRow.Delete – Farhan Oct 21 '17 at 19:06
  • `oRow` is a variable, so you need to declare it first and store a value to set the Row number and then delete it. –  Oct 22 '17 at 01:19