Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Macros for delete entire row in excel 2007

  1. #1

    Macros for delete entire row in excel 2007



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am attaching excel file.I want following using excel macros 2007.
    1) selection of sheet2
    2) Sheet has data in only one column "A"
    3) Entire range of data (data in column A only) to be selected using macro
    4) selection of black row in data base
    5) deleting blank row.
    6) I have seen that when macros run it slow down the process. If any trick please guide.

    kINDLY HELP.

    Regards,
    PN
    Attached Files Attached Files

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    824
    Articles
    0
    Excel Version
    Excel 2010
    In your attached file, Sheet2, column A..... the first blank cell is at row 2611.
    All the cells above that, appearing to be blank, actually contain a single space character.
    The delete key should be used to empty a cell, not the space bar.

    Maybe a macro along the lines of this ?
    Code:
    Sub DeleteBlankRows()
        Dim LastRow As Long
        Dim i As Long
    
    Application.ScreenUpdating = False
    With Sheets("Sheet2")
        LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        For i = LastRow To 1 Step -1
            If Trim(.Cells(i, 1).Value) = "" Then .Rows(i).EntireRow.Delete
        Next i
    End With
    Application.ScreenUpdating = True
    End Sub

  3. #3
    Acolyte jaslake's Avatar
    Join Date
    Aug 2011
    Location
    mineral city oh usa
    Posts
    81
    Articles
    0
    Excel Version
    2007;2010;MAc2011
    Hi pnnaik

    If I understand (not certain I do), try this
    Code:
    Option Explicit
    Sub Delete_Blank_Rows()
       Dim ws           As Worksheet
       Dim LR           As Long
       Application.ScreenUpdating = False
       Set ws = Sheets("Sheet2")
       With ws
          LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                           SearchDirection:=xlPrevious).Row
          .Range("A1:A" & LR).AutoFilter Field:=1, Criteria1:="="
          .Range("A2:A" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete
          .AutoFilterMode = False
       End With
    End Sub
    John

  4. #4
    Both Macros are working. Thanks for your kind reply.
    If I want to delete line with special character like ============================================================================ and
    ----------------------------------------------------------
    How to accomodate this in above macros. Kindly suggest.
    Regards,
    PN

  5. #5
    Acolyte jaslake's Avatar
    Join Date
    Aug 2011
    Location
    mineral city oh usa
    Posts
    81
    Articles
    0
    Excel Version
    2007;2010;MAc2011
    Hi pnnaik

    Try this
    Code:
    Option Explicit
    Sub Delete_Blank_Rows()
       Dim ws           As Worksheet
       Dim LR           As Long
       Application.ScreenUpdating = False
       Set ws = Sheets("Sheet2")
       With ws
          LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                           SearchDirection:=xlPrevious).Row
          .Range("A1:A" & LR).AutoFilter Field:=1, Criteria1:="="
          .Range("A1:A" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete
          .Range("A1:A" & LR).AutoFilter Field:=1, Criteria1:= _
                                         "=*-----*", Operator:=xlOr, Criteria2:= _
                                         "=*======*"
          .Range("A2:A" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete
          .AutoFilterMode = False
       End With
    End Sub
    John

  6. #6
    Thanks John.
    It is working. I will get back to you for help after final requirements.
    Regards,
    PN

  7. #7
    Acolyte jaslake's Avatar
    Join Date
    Aug 2011
    Location
    mineral city oh usa
    Posts
    81
    Articles
    0
    Excel Version
    2007;2010;MAc2011
    Your welcome...glad I could help.
    John

  8. #8
    I used both macros.
    But it takes more times when it has to process more than 25000 cells in column A.
    Also some time also sometimes machine get hanged. I am using excel-2007.

    I need following

    MACRO-1

    1) I want to trim all cells
    2) Want to delete blank cell, special character ======= ,and ------

    MACRO-2

    In addition to MACRO-1
    I want to keep only those cell which content

    "C O L U M N"
    "Fe*** (Main)" (here *** will take any numeric value)
    "CROSS SECTION"
    "% exceeds"
    "GUIDING"
    "REQD. STEEL AREA"
    "REQUIRED STEEL AREA"

    I know very less about VBA but I used to with excel function.
    Regards,
    PN

  9. #9
    Acolyte jaslake's Avatar
    Join Date
    Aug 2011
    Location
    mineral city oh usa
    Posts
    81
    Articles
    0
    Excel Version
    2007;2010;MAc2011
    Hi PN

    See if this Code in the attached does as you require. CTRL + x will fire the Code.
    Code:
    Option Explicit
    Sub Delete_Blank_Rows()
       Dim ws           As Worksheet
       Dim LR           As Long
       Dim TrimRng      As Range
       Application.ScreenUpdating = False
       Set ws = Sheets("Sheet1")
       With ws
          LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                           SearchDirection:=xlPrevious).Row
          .Range("A1:A" & LR).AutoFilter Field:=1, Criteria1:="="
          .Range("A2:A" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete
          .Range("A1:A" & LR).AutoFilter Field:=1, Criteria1:= _
                                         "=*-----*", Operator:=xlOr, Criteria2:= _
                                         "=*======*"
          .Range("A1:A" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete
          .AutoFilterMode = False
          LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                           SearchDirection:=xlPrevious).Row
          Set TrimRng = .Range("A1:A" & LR)
          Call TrimRange(TrimRng)
          Set TrimRng = Nothing
          Call test
       End With
    End Sub
    
    
    Sub TrimRange(ByRef MyRng As Range)
       Dim vY   'as Variant
       With MyRng
          vY = Evaluate("IF(ROW(" & .Address & "),TRIM(" & .Address & "))")
       End With
       MyRng.Resize(UBound(vY, 1), 1).Value = vY
    End Sub
    
    
    Sub test()
       Dim ws           As Worksheet
       Dim DataRng      As Range
       Dim CritRng      As Range
       Dim LR           As Long
       Dim LC           As Long
       Application.ScreenUpdating = False
       Set ws = Sheets("Sheet1")
       With ws
          LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                           SearchDirection:=xlPrevious).Row
          LC = .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, _
                           SearchDirection:=xlPrevious).Column + 1
          Set DataRng = .Range("A1:A" & LR)
          Set CritRng = .Cells(1, LC).Resize(2)
          Cells(2, LC).Formula = "=AND(ISNA(MATCH({""C O L U M N N O. "",""*Fe*(Main)*"",""*GUIDING* "",""*CROSS SECTION:* "",""*REQD. STEEL "",""*exceeds maximum limit*"",""** REQUIRED STEEL*""}&""*"",A2,0)))"
          With DataRng
             .AdvancedFilter xlFilterInPlace, CriteriaRange:=CritRng
             .Offset(1, 0).EntireRow.Delete
          End With
          .ShowAllData
          CritRng.EntireColumn.Delete
       End With
       Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files
    John

  10. #10
    John,

    This is what I want. Many thanks.
    Now I straight little more. With this data I want to apply formula in excel. As per QWE v1 file data is upto A305.

    1) Suppose I want to insert formula in column B,C,D.

    My problem is that If I put formula for up to B50000, c50000, D50000 CELL EXCEL take lot time to open as well for save the file. I have take upto 50000 as a example. Data may be up to 50 cells or more than 50000 cells.

    I want formula activate in cell B,C,D as per last used cell in A column. In our example case it is A305.

    Thanks again.
    Regards,
    PN

Page 1 of 2 1 2 LastLast

Posting Permissions

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