Macro: delete row on condition

[juliemaenhout]

Hi!

I have no experience whatsoever with macros and I only need them for preprocessing data for my master thesis. It would be great if any of you could give me a hand.

I have a data set of 150 000 rows in an Excel sheet. I'd like to remove all the rows where
1. the first character is non-numeric
2. the last character isn't the letter "N"

Could you help me out please?
Thanks in advance,

Julie

 

[Peter_SSs]

Welcome to the MrExcel board!

1. Are we only talking about looking at one column? If so, which column?

2. Apart from the column in 1 above (assuming it is only one), what other columns contain data?

3. What Excel version are you using?

 

[juliemaenhout]

Welcome to the MrExcel board!

1. Are we only talking about looking at one column? If so, which column?

2. Apart from the column in 1 above (assuming it is only one), what other columns contain data?

3. What Excel version are you using?

Thank you for the quick reply.

1. I'm only working in column A, but the data in this column should actually be spread over 13 columns. It is supposed to be a CSV file but I received the data all in one column, which is one of the reasons why I need to do the preprocessing.

2. See above.

3. I'm using Excel 2007.

 

[Peter_SSs]

I have a data set of 150 000 rows in an Excel sheet. I'd like to remove all the rows where
1. the first character is non-numeric
2. the last character isn't the letter "N"

Sorry, I should have also asked: Are we deleting rows where 1 AND 2 occur or where 1 OR 2 occurs?

 

[juliemaenhout]

We're deleting rows where 1 OR 2 occurs.

Thanks in advance for your help!

 

[Peter_SSs]

We're deleting rows where 1 OR 2 occurs.

Thanks in advance for your help!

OK, so we are keeping rows where the value starts with a number AND ends with an "N".

Try this in a copy of your workbook.
Assuming data starts in row 2.

Sub Del_Rws()
    Dim cols As Long, CoI As Long
    Dim Adr As String
    
    Const ColOfInterest As String = "A" '<- Change to suit
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    CoI = Columns(ColOfInterest).Column
    With ActiveSheet
      With Intersect(.UsedRange, .Rows("2:" & .Rows.Count))
        cols = .Columns.Count + 1
        Adr = .Columns(CoI).Address
        With .Resize(, cols)
          .Columns(cols).Value = Evaluate("=if(isnumber(left(" & Adr & ",1)+0),if(right(" & Adr & ",1)=""N"",1,""""),"""")")
          .Sort Key1:=.Cells(1, cols), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, _
            MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
          With .Columns(cols)
            On Error Resume Next
            .SpecialCells(xlBlanks).EntireRow.Delete
            On Error GoTo 0
            .ClearContents
          End With
        End With
      End With
    End With
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

 

[juliemaenhout]

Thank you so, so much! It worked perfectly!

 

[Peter_SSs]

Thank you so, so much! It worked perfectly!

Great news. Thanks for letting us know.