+ Reply to Thread
Results 1 to 5 of 5

Macro for deleting last row in all sheets except 3 sheets

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    02-04-2013
    Location
    Bangalore
    MS-Off Ver
    Excel 2010
    Posts
    100

    Macro for deleting last row in all sheets except 3 sheets

    sir i have a workbook
    with around 25- 30 sheets

    now i want a macro

    to delete last row of all sheets except 3 sheets with name
    "DATA" , " TRI" and "STOCKTEMP"
    in all remaing sheets check the value in "A" column last cell and delete the entire row

    thank you in advance
    harish

  2. #2
    Forum Contributor
    Join Date
    06-07-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    189

    Re: Macro for deleting last row in all sheets except 3 sheets

    Try

    Sub test()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        With ws
            Select Case .Name
                Case "DATA", " TRI", "STOCKTEMP"
                Case Else
                    .Range("A" & Rows.Count).End(xlUp).EntireRow.Delete
            End Select
        End With
    Next ws
    End Sub

  3. #3
    Forum Contributor
    Join Date
    02-04-2013
    Location
    Bangalore
    MS-Off Ver
    Excel 2010
    Posts
    100

    Re: Macro for deleting last row in all sheets except 3 sheets

    sir one more requset for the same file

    need to delete all rows seeing first empty cell in "D" column after D40 cell

    to delete rows of all sheets except 3 sheets with name
    "DATA" , " TRI" and "STOCKTEMP"
    in all remaing sheets
    sorry for distubing again when i was working with my file i was facing problem with this
    so late reply

    thank you

    harish

  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Macro for deleting last row in all sheets except 3 sheets

    Sub deleleall3()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim ws As Worksheet
        For Each ws In ThisWorkbook.Worksheets
          With ws
            If .Name <> "DATA" And .Name <> "TRI" And .Name <> "STOCKTEMP" Then
               .Range("A1", .Cells.SpecialCells(11)).EntireRow.Delete
            End If
          End With
        Next
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    End Sub

  5. #5
    Forum Contributor
    Join Date
    02-04-2013
    Location
    Bangalore
    MS-Off Ver
    Excel 2010
    Posts
    100

    Re: Macro for deleting last row in all sheets except 3 sheets

    excellent sir
    thank you for fast reply with answer

    harish

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1