Delete All Rows Except...

Hi All,

I'm new here.
I have a questions that i could not find the answer.
I'm also new in macro VBA programming.
The question is, I need to delete all rows in excel except the last row and any rows that contain this word "Blocked".
Anyone with brilliant idea?
Thanks :)

Comments

  • Hi

    Can you try similar to the one below

    [code]Sub DeleteRow()
    Dim i1 As Long
    Dim iMax As Long
    iMax = Cells.SpecialCells(xlCellTypeLastCell).Row

    For i1 = iMax - 1 To 1 Step -1
    If InStr(1, Cells(i1, 1), "Blocked") = 0 Then
    Rows(i1).EntireRow.Delete
    End If
    Next i1


    End Sub[/code]

    Cheers
    Shasur

    : Hi All,
    :
    : I'm new here.
    : I have a questions that i could not find the answer.
    : I'm also new in macro VBA programming.
    : The question is, I need to delete all rows in excel except the last
    : row and any rows that contain this word "Blocked".
    : Anyone with brilliant idea?
    : Thanks :)
    :

    VBA Tips & Tricks ([link=http://vbadud.blogspot.com]http://vbadud.blogspot.com[/link])

    C# Code Snippets ([link=http:dotnetdud.blogspot.com]http:dotnetdud.blogspot.com[/link])
  • Hi Shasur,

    Could you briefly explain what is the process of the code you've given?
    I try to run the code but it delete all the data in this file.
    Thanks



    : Hi
    :
    : Can you try similar to the one below
    :
    : [code]: Sub DeleteRow()
    : Dim i1 As Long
    : Dim iMax As Long
    : iMax = Cells.SpecialCells(xlCellTypeLastCell).Row
    :
    : For i1 = iMax - 1 To 1 Step -1
    : If InStr(1, Cells(i1, 1), "Blocked") = 0 Then
    : Rows(i1).EntireRow.Delete
    : End If
    : Next i1
    :
    :
    : End Sub[/code]:
    :
    : Cheers
    : Shasur
    :
    :
    : VBA Tips & Tricks
    : ([link=http://vbadud.blogspot.com]http://vbadud.blogspot.com[/link])
    :
    : C# Code Snippets
    : ([link=http:dotnetdud.blogspot.com]http:dotnetdud.blogspot.com[/link]
    : )


  • Hi

    The code gets the last row of the current sheet

    [code]iMax = Cells.SpecialCells(xlCellTypeLastCell).Row [/code]

    Then from the lastrow to the first row it checks the first column for the word blocked

    [code]InStr(1, Cells(i1, 1), "Blocked") [/code]
    IF the word is found in the first column it deletes the entire column




    : Hi
    :
    : Can you try similar to the one below
    :
    : [code]: Sub DeleteRow()
    : Dim i1 As Long
    : Dim iMax As Long
    : iMax = Cells.SpecialCells(xlCellTypeLastCell).Row
    :
    : For i1 = iMax - 1 To 1 Step -1
    : If InStr(1, Cells(i1, 1), "Blocked") = 0 Then
    : Rows(i1).EntireRow.Delete
    : End If
    : Next i1
    :
    :
    : End Sub[/code]:
    :
    : Cheers
    : Shasur
    :
    : : Hi All,
    : :
    : : I'm new here.
    : : I have a questions that i could not find the answer.
    : : I'm also new in macro VBA programming.
    : : The question is, I need to delete all rows in excel except the last
    : : row and any rows that contain this word "Blocked".
    : : Anyone with brilliant idea?
    : : Thanks :)
    : :
    :
    : VBA Tips & Tricks
    : ([link=http://vbadud.blogspot.com]http://vbadud.blogspot.com[/link])
    :
    : C# Code Snippets
    : ([link=http:dotnetdud.blogspot.com]http:dotnetdud.blogspot.com[/link]
    : )


    VBA Tips & Tricks ([link=http://vbadud.blogspot.com]http://vbadud.blogspot.com[/link])

    C# Code Snippets ([link=http:dotnetdud.blogspot.com]http:dotnetdud.blogspot.com[/link])
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories