Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Delete range of numbers

    I would like a macro to delete the following range of numbers in Col A

    1) 100001 to 199999
    2) Any number starting with an X for eg x21452

    Your assistance in this regard is most appreciated

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Howard

    The attached file does that.
    It just deletes the cell contents if value is:
    1) 100001 to 199999
    2) Any number starting with an x for eg x21452
    3) Any number starting with an X for eg X567

    zeddy
    Attached Files Attached Files

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How about this?

    Code:
    Option Explicit
    Sub DeleteThings()
      Dim lLastrow As Long
      Dim lRow As Long
      With ActiveSheet
        lLastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
        For lRow = 1 To lLastrow
          With .Cells(lRow, 1)
            If IsNumeric(.Value) Then
              If .Value >= 100001 And _
                .Value <= 199999 Then
                  .ClearContents
              End If
            End If
            If Application.WorksheetFunction.IsText(.Value) Then
              If Ucase(Mid(.Value, 1, 1)) = "X" And _
                Mid(.Value, 2, 1) >= "0" And _
                Mid(.Value, 2, 1) <= "9" Then
                    .ClearContents
              End If
            End If
          End With
        Next
      End With
    End Sub
    Steve
    Last edited by sdckapr; 2013-08-30 at 05:10. Reason: Added UCASE to make it case-insensitive. Thanks, zeddy

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    zeddy,
    Your code deletes anything starting with "X" (or "x") but does not test if the next item is a number or not (not sure how critical this is, but it was mentioned in the requirements [You also chose the "X" and "x", I accepted the criteria as given and only checked the upper case. If case is not important, UCASE(Mid(.Value, 1, 1)) = "X" can be substituted easily into my code

    Also using 65536 as the "last row" in a worksheet was a "standard practice" for pre-xl2007 workbooks, but I believe it should be avoided now since many people have started using XL2007 and higher which have a higher number of rows in the worksheet. It is a better practice (and works for all the versions) to test what the last row of the worksheet is and use that instead.

    Steve

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Steve

    I was trying to keep my code simple for Howard to follow.
    (he is trying to learn vba).
    I use 65536 to allow Excel2003 users to use it.
    Anyone using more than this number of rows of data would probably not be a novice Excel user, and would know what would be required.

    Cells(.Rows.Count, 1).End(xlUp).Row works for all versions, but requires more typing than
    zLastRow = [a65536].End(xlUp).Row
    ..which might be easier to understand for beginners.
    But, perhaps you are right, I will make myself use the longer version.
    Or I might not.

    But your code won't remove the entry
    x21452
    ..as specifically given in post#1

    zeddy
    Last edited by zeddy; 2013-08-30 at 05:06.

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Howard

    Which do you prefer:
    Steve's:
    Code:
    If Application.WorksheetFunction.IsText(.Value) Then
      If Mid(.Value, 1, 1) = "X" And _
        Mid(.Value, 2, 1) >= "0" And _
        Mid(.Value, 2, 1) <= "9" Then
            .ClearContents
      End If
    End If
    or mine:
    Code:
    If zValue Like "X*" Then
    cell.ClearContents
    End If

    zeddy

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    But your code won't remove the entry
    x21452
    ..as specifically given in post#1
    You are correct, I modified my code to make it case insensitive. Even after cataract surgery, the eyes still have problems sometimes....

    But as to the 65536 vs rows.count, yes it is longer, but the code is often used by people other than the OP and this type of "general practice" is learned as well. I have had to go back to several VB programs from the past and "correct" the 65536 since the newer version has allowed larger worksheets and the previous code was "adopted"

    Steve

    Steve

  8. #8
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy & Steve

    Thanks for the input. I sincerely appreciate the help. Zeddy's approach is more easier for me to follow at this stage as im trying to learn VBA.

    Steve Even though Zeddy's code is more easier for me to understand at this poimnt, I do value your input

    I need to code to be amended to do the following

    1) to delete the rows containing the range where 100001 to 199999 in in Col A
    2) My apology -the number containing the X is at the end of the number for eg 13030X-Where the nuners have an X at the end in Col A the entire row must also be deleted

    Howard

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How about:
    Code:
    Option Explicit
    Sub DeleteRowsThings()
      Dim lLastrow As Long
      Dim lRow As Long
      With ActiveSheet
        lLastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
        For lRow = 1 To lLastrow
          With .Cells(lRow, 1)
            If IsNumeric(.Value) Then
              If .Value >= 100001 And _
                .Value <= 199999 Then
                  .EntireRow.Delete
              End If
            End If
            If Application.WorksheetFunction.IsText(.Value) Then
              If UCase(Right(.Value, 1)) = "X" Then
                .EntireRow.Delete
              End If
            End If
          End With
        Next
      End With
    End Sub
    Steve

  10. #10
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    Thanks for the help. When running the macro it comes up with error object required and the following code is highlighted

    If Application.WorksheetFunction.IsText(.Value) Then

    It will be appreciated if you can correc and advise

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Could you attach a workbook with the problem? I can not replicate it.

    You could just try
    Code:
    Option Explicit
    Sub DeleteRowsThings()
      Dim lLastrow As Long
      Dim lRow As Long
      With ActiveSheet
        lLastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
        For lRow = 1 To lLastrow
          With .Cells(lRow, 1)
              If .Value >= 100001 And _
                .Value <= 199999 Then
                  .EntireRow.Delete
              End If
              If UCase(Right(.Value, 1)) = "X" Then
                .EntireRow.Delete
              End If
          End With
        Next
      End With
    End Sub
    and eliminate the testing whehter it is a number before comparing numbers or a text before reading the strings. It seems to work OK without it so it seems I may have been a little too anal and over-tested. I seem to recall problems in VB if I was comparing test to numbers, but perhaps this was earlier VB versions or I am having a brain-fart [these happen more and more frequently as I get older]...

    Steve

  12. #12
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    Attached please find sasmple data with macro

    It would be appreciated if you would test & correct

    Howard
    Attached Files Attached Files
    Last edited by HowardC; 2013-08-30 at 09:42.

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Try this.
    Code:
    Option Explicit
    Sub DeleteRowsThings()
      Dim lLastrow As Long
      Dim lRow As Long
      Dim vValue
      With ActiveSheet
        lLastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
        For lRow = lLastrow To 1 Step -1
          With .Cells(lRow, 1)
              vValue = .Value
              If vValue >= 100001 And _
                vValue <= 199999 Then
                  .EntireRow.Delete
              End If
              If UCase(Right(vValue, 1)) = "X" Then
                .EntireRow.Delete
              End If
          End With
        Next
      End With
    End Sub
    The first issue was that rows need to be deleted from the bottom up. The other issue (causing the error) was once a row was deleted, it tried to check the value of the (now) non-existent item for an "X" at the end, so it gave the object not found error. I fixed it by putting the value of the cell into a variable and checking that instead of relooking up the value of the (non-existent)cell.

    Steve

  14. #14
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    It works perfectly now. Thanks for the help, much appreciated

    It would be appreciated if you can make one small amendment. I found a few items where the number in Col A begins with an X.

    I also want these rows deleted

    Kindly amend your code to accomodate this

    Howard

  15. #15
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    I have resolved this. Added add line of code

    If UCase(left(vValue, 1)) = "X" Then
    .EntireRow.Delete
    End If


    Howard

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •