visual basic for excel

[visualcomplicated]

vb code question. i am trying to understand how to get a macro using vb to:
if i have a column of numbers, go through the whole column and identify any number that is less than say 10,000 and then select the entire row that number is on and delete the entire row. continue the process through the entire column. also, some cells in the column will be blank (delete), words (delete), and symbols such as # (delete).

i'm a relative novice here so any help would be appreciated.

Thanks,
MDC

[MikeTheBike]

vb code question. i am trying to understand how to get a macro using vb to:
if i have a column of numbers, go through the whole column and identify any number that is less than say 10,000 and then select the entire row that number is on and delete the entire row. continue the process through the entire column. also, some cells in the column will be blank (delete), words (delete), and symbols such as # (delete).

i'm a relative novice here so any help would be appreciated.

Thanks,
MDC

Yes it can be done but one question, if some cells can be blank, how do we know where the data ends?

MTB

[visualcomplicated]

Well, the data will vary day to day. I think, however, it will never go beyond 5,000 rows. So maybe the macro should go to A5000 then stop?

Thanks for the help.

Matthew

[Killer42]

Not sure whether it might take too long to run, but you try something like this...

Dim C As Range
For Each C In Range("A:A")
  ' This loop will execute for each cell in column A...
Next

I've forgotten for the moment how to check whether they're numeric/null/etc and delete the rows. Plus, I've just typed this here without testing it. But the logic may help get you started.

[MikeTheBike]

Hi

I think this should do it

Sub RemoveData()
    Dim i As Long
    Dim iLastRow As Long
    Dim iCheckCol As Integer
 
    iCheckCol = 5 'COLUMN BEING CHECKED
    iLastRow = ActiveCell.SpecialCells(xlLastCell).Row
    iLastRow = iLastRow - 1
 
    For i = iLastRow To 2 Step -1
        If Not IsNumeric(Cells(i, iCheckCol)) Or Cells(i, iCheckCol) = "" Then
            Rows(i).Delete
        End If
    Next i
End Sub

Note this deletes from the bottom, but not Row 1(?).


MTB

[Killer42]

... Note this deletes from the bottom, but not Row 1(?).

Thanks Mike. That's an important point which is often overlooked (and accounts for a number of enquiries that have been made here in the past).

If you start from the top, of course, then each time you delete one, you may miss the following one.