Delete rows based on left two numbers

Panoos64 · Aug 29, 2017

Hi all, I would like to write a vba code so that to “run” through col. “A” and where the left 2 numbers are “85” should delete the whole row. In 1^st schedule is an extract of original data and in 2^nd is the expected result. Many thanks in advance.

	A	B	C	D	E	F
1	Account	Debtor's Description	Balance Due	30 Days Due	60 Days Due	90 Days Due
2	8125005
3	8130025
4	8210001
5	8210002
6	8505005
7	8506010

	A	B	C	D	E	F
1	Account	Debtor's Description	Balance Due	30 Days Due	60 Days Due	90 Days Due
2	8125005
3	8130025
4	8210001
5	8210002

Joe4 · Aug 29, 2017

Are your values in column A numbers (entered as numbers, which usually right-justify), or are they numbers entered as text (which usually left-justify)?

Joe4 · Aug 29, 2017

Here are two versions of VBA code, one if the numbers are numeric, the other if they are text:

Code:

Sub DeleteRowsNum()
'   Delete all rows where value in column a if value is greater than or equal to 8500000
'   (numeric entry version)

    Dim dataRange As Range
    
'   Find data range (contiguous, starting in cell A1)
    Set dataRange = Range("A1").CurrentRegion


'   Filter to only show values over 8500000
    dataRange.AutoFilter Field:=1, Criteria1:=">=8500000", Operator:=xlAnd

'   Delete shown values
    Application.DisplayAlerts = False
    ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
    Application.DisplayAlerts = True
    
'   Remove filter
    Range("A1").AutoFilter

End Sub

Code:

Sub DeleteRowsText()
'   Delete all rows where value in column a if value is greater than or equal to 8500000
'   (text entry version)

    Dim dataRange As Range
    
'   Find data range (contiguous, starting in cell A1)
    Set dataRange = Range("A1").CurrentRegion

'   Filter to only show values over 8500000
    dataRange.AutoFilter Field:=1, Criteria1:="=85*", Operator:=xlAnd

'   Delete shown values
    Application.DisplayAlerts = False
    ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
    Application.DisplayAlerts = True
    
'   Remove filter
    Range("A1").AutoFilter

End Sub

Panoos64 · Aug 29, 2017

Thank you Joe4, both codes works, but i done a mistake when i described my project. Just i wanted to delete the rows (codes) which are belong to category "85" and no other. Below of " 85" i have another category "86" and base on above codes it deletes both categories. Apologies for my fault. I would be greatly appreciated if you could modified the above. Thanks once again.
Hv a nice day

Rick Rothstein · Aug 29, 2017

Does this macro do what you want...

Code:

Sub DeleteAcctNumsStartingWith85()
  Columns("A").Replace "85*", "#N/A", xlWhole, , , , False, False
  On Error Resume Next
  Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  On Error GoTo 0
End Sub

Panoos64 · Aug 30, 2017

Thank you Rick, it works perfect. Have a nice day

Joe4 · Aug 30, 2017

Note, in regards to the code I posted, it is a simple change to do what you want.

Just change this block:

Code:

'   Filter to only show values over 8500000
    dataRange.AutoFilter Field:=1, Criteria1:=">=8500000", Operator:=xlAnd

to this:

Code:

'   Filter to only show values over 8500000 and less than 8600000
    dataRange.AutoFilter Field:=1, Criteria1:=">=8500000" _
        , Operator:=xlAnd, Criteria2:="<8600000"

Panoos64 · Aug 30, 2017

Joe4 thanks, it works perfect and is a simple code. Thanks once again

Have a nice day

Delete rows based on left two numbers

Panoos64

Well-known Member

Excel Facts

Joe4

MrExcel MVP, Junior Admin

Joe4

MrExcel MVP, Junior Admin

Panoos64

Well-known Member

Rick Rothstein

MrExcel MVP

Panoos64

Well-known Member

Joe4

MrExcel MVP, Junior Admin

Panoos64

Well-known Member

Similar threads

Forum statistics

Share this page

Delete rows based on left two numbers

Well-known Member

Excel Facts

MrExcel MVP, Junior Admin

MrExcel MVP, Junior Admin

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP, Junior Admin

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock