Delete Certain Rows Only

[WESTERNWALL]

Hi
I have a spreadsheet where column B contains the following:

Lolos
9562981 052-
Vat No. 557796976

M.b.Glatt Of Ltd.
02-6563990 052
Vat No. 511740094

M.D.N sachar mazon
050-4084459* 050-79
Vat No. 514318187

Columns A and C have other information not important to this exercise.

I need to be able to delete the entire row where cells in column B are empty or have numbers only, but not alphanumeric or letters and punctuation marks only:

The above example that are three sets of rows out of maybe 600 or more similar ones. In a nutshell the first and third rows should not be cancelled: The second and empty rows should be cancelled.

Is there such an animal?

Thanks

Brian

 

[GlennUK]

Put an extra column in and fill with =MOD(ROW(),2) and then filter on either 1 or zero depending on where your data starts, so that only the unwanted rows show ... select them all and delete. Reset the filter. Remove the temporary column.

 

[WESTERNWALL]

Thanks, Glenn. Works like a charm. Can you put that into a macro? I need to build the programme for a colleague that just wants to press a button.

Thanks

Brian

 

[GlennUK]

The easiest way to get a macro is to use the macro recorder, and do the process by hand while you are recording. You may need to tweak the macro a little. Post back if you have any problems with it.

 

[WESTERNWALL]

Thanks Glenn, I'll try that.

 

[Weaver]

The macro recorder might be easy, but it's not very tidy. Having said that, it's a good place to start if you're learning about macros.

I'd imagine you'll be back with more questions once you've done this, like how can you make it fit varying data sizes, or simply just how to tidy it up.

Try:

Sub test()
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    Application.ScreenUpdating = False
    Columns(1).Insert xlToRight
    Range("A1").Value = "Filter"
    Range("A2:A" & lr).Formula = "=mod(row(),2)"
    With Range("A1")
        .Value = "Filter"
        .AutoFilter Field:=1, Criteria1:="0" 'change to 1 if you want the 'other' rows to be deleted
    End With
    Range("A2:A" & lr).EntireRow.Delete
    Cells.AutoFilter
    Columns(1).Delete xlToLeft
End Sub

 

[WESTERNWALL]

Weaver, thanks it worked beautifully. However, there is a small issue: I'll send you the details later, if you don't mind.

Thanks

 

[WESTERNWALL]

As I said the code works well. However, there is a snag. The rows with the numbers only and the empty ones still appear on the spreadsheet, but seperated from the two rows I need ie the row with the name and the row with the alphanumeric. I need to get the unwanted rows deleted.

The spreadsheet looks like this after Macro has been activated:

number row (unwanted)
empty row (unwanted)

number row (unwanted)
empty row (unwanted)

name row
alphanumeric row
name row
alphanumeric row

number row (unwanted)
empty row (unwanted)

number row (unwanted)
empty row (unwanted)

name row
alphanumeric row
name row
alphanumeric row
name row
alphanumeric row
name row
alphanumeric row

number row (unwanted)
empty row (unwanted)

number row (unwanted)
empty row (unwanted)



They are in groups, some at the top of the list, some in the middle and some at the end.

Am I making myself clear?

Thanks

 

[Weaver]

So what you're saying is it's no good just deleting every other row, as somewhere the pattern 'breaks'.

I'm guessing the only true answer to this will be to delete all the blank rows (easy) then delete all the rows with non alphabet characters. I'm saying it this way, rather than just numbers, since your 'all numbers' data also has dashes in it.

 

[Weaver]

Give this a go

Sub removeBlankAndDigitRows()
    Dim r As Long, c As Long, lr As Long
    c = 2
    Columns(c).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    lr = Cells(Rows.Count, c).End(xlUp).Row
    With CreateObject("vbscript.regexp")
        .Global = True
        .Pattern = "[\0-9]"
        For r = lr To 2 Step -1
            If .Replace(Cells(r, c).Value, "") = "" Then
                Rows(r).EntireRow.Delete
            End If
        Next r
    End With
End Sub