TechSpot

Macro help

By TS | Thomas
Apr 27, 2004
  1. Hey. I'm trying to get a Macro to delete a row in Column A "if" it includes the number 9xxxxxx, e.g. 9000058. I also currently have it set to delete any rows where Column A is blank, or Column A is qqqqqqqq. These 2 work just fine, but I can't seem to get the 9xxxxxx deletion to work. Does anyone know how to get the Macro to look for wildcards? Here's the relevent section of the macro I'm using;

    Dim rng As Range, cell As Range, del As Range
    Set rng = Intersect(Range("A:A"), ActiveSheet.UsedRange)
    For Each cell In rng
    If (cell.Value) = "" _
    Or (cell.Value) = "9******" _
    Or (cell.Value) = "qqqqqqqq" Then
    If del Is Nothing Then
    Set del = cell
    Else: Set del = Union(del, cell)
    End If
    End If
    Next cell
    On Error Resume Next
    del.EntireRow.Delete
     
  2. Mictlantecuhtli

    Mictlantecuhtli TS Evangelist Posts: 4,345   +11

    I'm not sure which language you're using, but if cell.Value is a string, you could try with a function that gets the leftmost character(s) from a string.
     
  3. Nodsu

    Nodsu TS Rookie Posts: 5,837   +6

    Yes. Try testing for cell.Value[0]="9" or however you reference characters in strings in VB
     
  4. TS | Thomas

    TS | Thomas TS Rookie Topic Starter Posts: 1,319

    Thanks, I don't really know how to do that yto be honest. I'm basically reverse engineering stuff I've found on google ;) But I have made more progress in another area at least so this is really all I have to figure out. It's possible that there could well be other numbers beginning with 9, e.g. 990057 that wouldn't need to be deleted though so I can't just blanket delete anything that starts with 9. It's pretty minor though really.
     
  5. Nodsu

    Nodsu TS Rookie Posts: 5,837   +6

    If you know what sort of numbers you don't want deleted then you can just add another test before the deletion test. Like:

    if (cell.value.length()=7) and (cell.value[0]) then

    to make sure you delete only 7 digit numbers beginning with 9
     
  6. TS | Thomas

    TS | Thomas TS Rookie Topic Starter Posts: 1,319

    Nope, I just get compile errors trying to use those. The value must always be 7 numbers long, with 9 being the first number. I'm googling away at it now
     
  7. Nodsu

    Nodsu TS Rookie Posts: 5,837   +6

    I never said these lines are syntactically correct :p
    Just trying to give you the general idea here.
     
  8. TS | Thomas

    TS | Thomas TS Rookie Topic Starter Posts: 1,319

    Nope, I need to be spoon fed the answer here :)
     
Topic Status:
Not open for further replies.

Similar Topics

Add New Comment

You need to be a member to leave a comment. Join thousands of tech enthusiasts and participate.
TechSpot Account You may also...