Macro: delete row on condition

juliemaenhout

New Member
Joined
Oct 7, 2014
Messages
5
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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,202
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 7, 2014
Messages
5
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,202
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 7, 2014
Messages
5

ADVERTISEMENT

We're deleting rows where 1 OR 2 occurs.

Thanks in advance for your help!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,202
Office Version
  1. 365
Platform
  1. Windows
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.

Rich (BB code):
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,816
Messages
5,525,064
Members
409,617
Latest member
Lenaf

This Week's Hot Topics

Top