none
Need help to create macro to delete rows RRS feed

  • Question

  • I have multiple spreadsheets and need to delete certain rows.  Doesn't need to meet any conditions...just delete the entire row.   Please help
    Monday, May 19, 2014 2:22 PM

All replies

  • There's almost an infinite number of directions you can go from here.  Maybe you should look at this link to get some ideas fo what you can do.

    https://www.udemy.com/blog/excel-vba-delete-row/

    Post back with specific question if you need more help with this concept.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Proposed as answer by ryguy72 Monday, May 19, 2014 5:55 PM
    Monday, May 19, 2014 3:48 PM
  • Here is the macro that was written a while back to delete rows.  I have over 500 worksheets that I need to delete the same rows to all.  When I run the macro, it doesn't do anything...hope u can help

    Sub RemoveLines()
        Dim MyPath As String, FilesInPath As String
        Dim MyFiles() As String, Fnum As Long
        Dim mybook As Workbook
        Dim CalcMode As Long
        Dim sh As Worksheet
        Dim ErrorYes As Boolean

        'Fill in the path\folder where the files are located
        MyPath = "C:\Data"

        
        If Right(MyPath, 1) <> "\" Then
            MyPath = MyPath & "\"
        End If

        'If there are no Excel files in the folder exit the sub
        FilesInPath = Dir(MyPath & "*.xl*")
        If FilesInPath = "" Then
            MsgBox "No files found"
            Exit Sub
        End If

        'Fill the array(myFiles)with the list of Excel files in the folder
        Fnum = 0
        Do While FilesInPath <> ""
            Fnum = Fnum + 1
            ReDim Preserve MyFiles(1 To Fnum)
            MyFiles(Fnum) = FilesInPath
            FilesInPath = Dir()
        Loop

        'Change ScreenUpdating, Calculation and EnableEvents
        With Application
            CalcMode = .Calculation
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
            .EnableEvents = False
        End With

        'Loop through all files in the array(myFiles)
        If Fnum > 0 Then
            For Fnum = LBound(MyFiles) To UBound(MyFiles)
                Set mybook = Nothing
                On Error Resume Next
                Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
                On Error GoTo 0

                If Not mybook Is Nothing Then


                    'Change cell value(s) in one worksheet in mybook
                    On Error Resume Next
                    With mybook.Worksheets(2)
                        If .ProtectContents = False Then
                           .Range("B8").Select
                           .ActiveCell.FormulaR1C1 = "xxxxxxx"
                           .Range("A1").Value = "ISeek Operating Plans"
                           .Range("A6:A7").EntireRow.Delete
                           .Range("A17:A31").EntireRow.Delete
                           .Range("A315:A333").EntireRow.Delete
                          '.Range("A298:A318").EntireRow.Delete
                            
                        Else
                            ErrorYes = True
                        End If
                    End With


                    If Err.Number > 0 Then
                        ErrorYes = True
                        Err.Clear
                        'Close mybook without saving
                        mybook.Close savechanges:=False
                    Else
                        'Save and close mybook
                        mybook.Close savechanges:=True
                    End If
                    On Error GoTo 0
                Else
                    'Not possible to open the workbook
                    ErrorYes = True
                End If

            Next Fnum
        End If

        If ErrorYes = True Then
            MsgBox "There are problems in one or more files, possible problem:" _
                 & vbNewLine & "protected workbook/sheet or a sheet/range that not exist"
        End If

        'Restore ScreenUpdating, Calculation and EnableEvents
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
            .Calculation = CalcMode
        End With
    End Sub

    Monday, May 19, 2014 4:47 PM
  • What do you mean it doesn't do anything?

    I got this.

    Err.Description
    Object doesn't support this property or method

    Err.Number
     438

    if all you want to do is delete rows, maybe you should simplify your code just a bit:

                    'Change cell value(s) in one worksheet in mybook
                    On Error Resume Next
                    With mybook.Worksheets(1)
                           .Range("A6:A7").EntireRow.Delete
                           .Range("A17:A31").EntireRow.Delete
                           .Range("A315:A333").EntireRow.Delete
                    End With


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Proposed as answer by ryguy72 Monday, May 19, 2014 5:55 PM
    Monday, May 19, 2014 5:06 PM
  • You just saved me from a lot of work..THANK U!!! THANK U!!!
    Monday, May 19, 2014 5:18 PM
  • Sure thing.  Glad it worked out for you.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Monday, May 19, 2014 5:55 PM