›

Delete All Rows Except...

fawchertfawchert
in VBA
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

  • shasurshasur
    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])
  • fawchertfawchert
    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]
    : )


  • shasurshasur
    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!

Sign In Register

Categories

In this Discussion