Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2008

    Unanswered: Entire row select and delete !


    I've got a large Excel file that I need to keep 1 record in 7.

    So I'll run thru it with a Rows = 1 to 100000 loop.

    I'll select an entire row if one cell meets a certain criteria and delete the entire row.

    What's the command for selecting entire rows and is it possible to delete such selections ?

    Please the commands.

    I thought it was

    but that doesn't compile.

    I'd like the row to dissappear so I might use a shift 1up command.

    All ideas welcome.



  2. #2
    Join Date
    May 2005
    Nevada, USA
    Provided Answers: 6
    I'm not strong in Excel VBA, but when I need to figure out how to do something, recording a macro and viewing the resulting code usually works. Here's the code from my deleting row 11:

    Selection.Delete Shift:=xlUp

    See if that helps.

  3. #3
    Join Date
    Oct 2003
    Howdy. Brett (at VBAExpress) provided a complete solution for this:

    Sub KillRows() 
        Dim MyRange As Range, DelRange As Range, C As Range 
        Dim MatchString As String, SearchColumn As String, ActiveColumn As String 
        Dim FirstAddress As String, NullCheck As String 
        Dim AC 
         'Extract active column as text
        AC = Split(ActiveCell.EntireColumn.Address(, False), ":") 
        ActiveColumn = AC(0) 
        SearchColumn = InputBox("Enter Search Column - press Cancel to exit sub", "Row Delete Code", ActiveColumn) 
        On Error Resume Next 
        Set MyRange = Columns(SearchColumn) 
        On Error Goto 0 
         'If an invalid range is entered then exit
        If MyRange Is Nothing Then Exit Sub 
        MatchString = InputBox("Enter Search string", "Row Delete Code", ActiveCell.Value) 
        If MatchString = "" Then 
            NullCheck = InputBox("Do you really want to delete rows with empty cells?" & vbNewLine & vbNewLine & _ 
            "Type Yes to do so, else code will exit", "Caution", "No") 
            If NullCheck <> "Yes" Then Exit Sub 
        End If 
        Application.ScreenUpdating = False 
         'to match the WHOLE text string
        Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole) 
         'to match a PARTIAL text string use this line
         'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlpart)
         'to match the case and of a WHOLE text string
         'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True)
        If Not C Is Nothing Then 
            Set DelRange = C 
            FirstAddress = C.Address 
                Set C = MyRange.FindNext(C) 
                Set DelRange = Union(DelRange, C) 
            Loop While FirstAddress <> C.Address 
        End If 
         'If there are valid matches then delete the rows
        If Not DelRange Is Nothing Then DelRange.EntireRow.Delete 
        Application.ScreenUpdating = True 
    End Sub
    old, slow, and confused
    but at least I'm inconsistent!

    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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