How to delete the last unwanted rows

**zaska** · 03-17-2013, 07:29 AM

Hello,

I have Sorted list of data with serial numbers. My task is to identify the last serial number which is 8 in the below and delete all the rows to the end after the last serial number. Can anyone look if it can be done using a VBA?

S.NO
1
2
3
4
5
6
7
8
BRANCH-NAME
CONDITION IS DISPLAYED FOR
CONDITION IS DISPLAYED FOR
CUSTOMER-NAME
CUSTOMER-NAME
LIST
LIST

Regards,

Zaska

**Richard Buttrey** · 03-17-2013, 07:38 AM

Hi,

You don't really need a macro, although you could of course encode the process below in a macro.

Enter in B2 and copy down

Formula:



=IF(ISNUMBER(A2),"Keep","Delete")

Then filter column B for the word 'Delete', select all the filtered rows and hit the delete key.

**zaska** · 03-17-2013, 07:43 AM

Sir,

Thanks for the suggestion. Could you please tell me how to encode the same process into this macro. As i have to deal with many excel sheets iam looking for a vba.

Option Explicit

Sub ReformatData()
Dim delRng As Range, LR As Long, Rw As Long
 
    LR = Range("A" & Rows.Count).End(xlUp).Row
    For Rw = 1 To LR
        If Left(Range("C" & Rw), 3) = "---" Or IsEmpty(Range("C" & Rw)) Then
            If delRng Is Nothing Then
                Set delRng = Range("C" & Rw)
            Else
                Set delRng = Union(delRng, Range("C" & Rw))
            End If
        End If
    Next Rw
    If Not delRng Is Nothing Then delRng.EntireRow.Delete xlShiftUp
    Columns.AutoFit
    Range("A1").CurrentRegion.Sort Range("A2"), xlAscending, Header:=xlYes
End Sub

Thank you

**Richard Buttrey** · 03-17-2013, 08:02 AM

Hi,

Your macro does not appear to contain the same data as your example. For instance you refer to column C in your macro, but there is nothing in column C. Similarly there are no cells with '----' in them.

If you are seeking answers here it's incumbent on you to let us have all relevant information.
The sort of looping macro you have in mind is not the most efficient way. Can I suggest you manually try the process I gave you and if this works then let me know and I'll encode that in a macro for you. Otherwise provide an example of your real data which includes all possible conditions.

**zaska** · 03-17-2013, 08:18 AM

Hello,

I am herewith attaching the example file which includes the real data. I want to sort the data according to the serial number in column a and delete the rest of the unwanted rows after sorting.

Thank you

**Richard Buttrey** · 03-17-2013, 09:11 AM

It has come to my attention that this is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed.

Thread Closed.

**dilipandey** · 03-17-2013, 08:01 AM

Hi zaska,

Try below code:-

Sub DILIPandey()
'checking data and removing rows
Cells(1, 1).Select
Selection.End(xlDown).Select
    For n = 1 To Selection.Row - 1
        If Application.WorksheetFunction.IsText(Selection) = True Then
        Selection.EntireRow.Delete
        Selection.Offset(-1, 0).Select
        Else
        Selection.Offset(-1, 0).Select
        End If
    Next
End Sub

see attachment:- Delete rows after heading.xlsm

Regards,
DILIPandey
<click on below * if this helps>

**jindon** · 03-17-2013, 09:07 AM

Is this what you are after?

Option Explicit

Sub test()
    Dim rng As Range
    With ActiveSheet.UsedRange.Columns(1)
        On Error Resume Next
        Union(.SpecialCells(2, 2), .SpecialCells(4)).EntireRow.Delete
        On Error GoTo 0
        With .Parent.Cells(1).CurrentRegion
            .Sort .Cells(1), 1
        End With
    End With
End Sub