VBA - row deletion variables

dstepan

Board Regular
Joined
Apr 16, 2009
Messages
160
Is it possible to delete multiple rows based on the following?

My original file is about 50,000 lines and each of the scenarios below could have hundreds of rows each.

1) If the first digit of the four digit number is a 1, 2, 3, 4, 5, or 9
2) If the 7 digit number is 6141 432
3)If the range of 7 digit numbers run from 7381 150 through 7381 179

For example:

Before
Column A Column B
1001 431
2004 180
6007 223
6141 432
7008 111
7299 199
7381 150
7381 180
9001 999

After
Column A Column B
6007 223
7008 111
7299 199
7381 180
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,249
Office Version
  1. 365
Platform
  1. Windows
So, are there 4 numbers in column A and then 3 numbers in column B (you say a 7 digit number, but it really looks like a 4 and a 3 digit number)?

Also, are these values entered as numbers or text? An easy way to tell (provided you haven't selected a justification formatting option) is that text entries are typically left-justified within a cell, while numeric ones are right-justified.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,865
Office Version
  1. 365
Platform
  1. Windows
This is based on having a 4 digit number in column & a 3 digit number in B, starting in A1
Code:
Sub dstepan()

    Dim Rng As Range
    Dim Col As Range

    Set Col = Range("A1:A9")
    For Each Rng In Col
        If Rng Like "[1-5,9]*" Then Rng.Clear
        Select Case Rng.Value & Rng.Offset(, 1).Value
        Case Is = 6141432
            Rng.Clear
        Case 7381150 To 7381179
            Rng.Clear
        End Select
    Next Rng
    Columns("A").SpecialCells(xlBlanks).EntireRow.Delete
End Sub
 
Last edited:

dstepan

Board Regular
Joined
Apr 16, 2009
Messages
160
Thank you for the help! this works splendidly. However, after the code is run I would like to also delete any non 4 digit number in column A.

Is this even possible?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,865
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Do you just want to delete the number, or the entire row?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,865
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
Sub dstepan()

    Dim Rng As Range
    Dim Col As Range

    Set Col = Range("A1:A9")
    For Each Rng In Col
        [COLOR=#ff0000]If Len(Rng) <> 4 Then Rng.Clear[/COLOR]
        If Rng Like "[1-5,9]*" Then Rng.Clear
        Select Case Rng.Value & Rng.Offset(, 1).Value
        Case Is = "6141432"
            Rng.Clear
        Case 7381150 To 7381179
            Rng.Clear
        End Select
    Next Rng
    Columns("A").SpecialCells(xlBlanks).EntireRow.Delete
End Sub
Added the line in red
 

Forum statistics

Threads
1,136,214
Messages
5,674,431
Members
419,508
Latest member
trinstrick

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top