Delete rows based on criteria.

[leelabird]

Hi everyone,
I hope that someone can help me with a little problem.
I have an Excel file into which data is dumped daily to sheet1. In each cell in column H, there are text values and numeric values. There is never a combination of both (alpha numeric). I wish to delete all rows associated with the text values only automatically. We currently do this manually and it is very time consuming as there are a varying number of rows but rarely more than 5000. The columns range from A to AK.
Thank you for any assistance.
Regards,
Leela.

 

[Joe4]

Welcome to the Board!

Try this code:

Sub MyDeleteRows()

    Dim lrow As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row with data in column H
    lrow = Cells(Rows.Count, "H").End(xlUp).Row
    
'   Loop through all rows backwards
    For r = lrow To 1 Step -1
'       Delete text rows
        If Not IsNumeric(Cells(r, "H")) Then Rows(r).Delete
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[farmerscott]

Hi Leela,

Welcome to MrExcel.

Try this code-

Sub remove_text()
'assumes headers in row 1.
Dim lr As Long
Dim x As Long


lr = Worksheets("Sheet1").Cells(Rows.Count, "H").End(xlUp).Row

With Sheets("Sheet1") 'change name to suit.
For x = lr To 2 Step -1
    y = Cells(x, 8).Value
    If WorksheetFunction.IsNumber(y) = False Then
    .Rows(x).EntireRow.Delete
    
    End If
    Next x
End With
End Sub

I have assumed Col H is the longest column.

I have also assumed that your sheet1 is literally "Sheet1".

cheers,

FarmerScott

 

[vcoolio]

Hi Leela,

Here' another method:-

Sub DeleteRws()

Range("H2", Range("H" & Rows.Count).End(xlUp)).SpecialCells(2, 2).EntireRow.Delete

End Sub

I hope that this helps.

Cheerio,
vcoolio.

 

[leelabird]

Hi there Joe, Farmer Scott & Vcoolio.
Thank you all for the replies. I'm now spoilt for choice! All the codes supplied work wonderfully.
@ Vcoolio:
Wow! So short, sweet and to the point! It works exceptionally well and in an instant.
I'll more than likely use your code but will certainly store all of them away for future reference.
Many thanks again to you all for being so kind to a complete stranger.

Regards,
Leela

 

[vcoolio]

You're welcome Leela.
Glad that we were able to help.

Cheerio,
vcoolio.