Results 1 to 9 of 9
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,421
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Macro to delete Numbers

    I have the period number in Col O. I would like delete the numbers less than than a particular number For Eg if I am in period 12 , I would like to delete all the numbers less than 12.

    Your assistance in this regard is mst appreciated

  2. #2
    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
    What cells would you like to delete? [I am presuming particular columns in each row based on the value of Col O.] Nut I think you need to supply more information.

    If you want to delete the values in Col O < 12, you can do it easily by autofiltering on the row choosing to filter on <12 and then delete the cells or even the entire rows of those displayed.

    Steve

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

    Thanks for the reply. Attached please find sample data

    Howard
    Attached Files Attached Files

  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
    What should the results be? can you just filter column O and delete the contents or the whole row? It does not need a macro...

    Steve

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

    The results in this case must only show period 12 iin Col O. Would it not be easier to set up a sort macro and the manually delete the items less than 12?

    Howard

  6. #6
    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
    No the autofilter would be faster and easier. Have you tried setting the filter to display only the values <12 and then delete them? Your data already has the filter on it. Why create a macro for what will amount to a simple task that Excel's built-in features can do?

    But a macro to delete the rows with Col O <12 would be:
    Code:
    Option Explicit
    Sub ClearColOLT12()
      Dim lLastRow As Long
      With ActiveSheet
        lLastRow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
        .Range("a1").AutoFilter Field:=15, Criteria1:="<12"
        .Range("O2:O" & lLastRow).EntireRow.Delete
        .ShowAllData
      End With
    End Sub
    If you only wanted to clear Col O (not delete the rows) you could use the line instead:
    .Range("O2:O" & lLastRow).ClearContents

    But as I said, the task could have been done faster manually. I think it could be done manually faster than it would take you copy and paste the macro, let alone creating it...
    Steve

  7. The Following User Says Thank You to sdckapr For This Useful Post:

    HowardC (2013-10-19)

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

    Thanks for the help

    Howard

  9. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Here is the addition of a couple extra lines to automatically determine the max period

    Code:
    Option Explicit
    Sub ClearColOLT12()
      Dim lLastRow As Long
      Dim period As Integer
      With ActiveSheet
        lLastRow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
        period = WorksheetFunction.Max(Range("O1:O" & lLastRow))
        .Range("a1").AutoFilter Field:=15, Criteria1:="<" & period
        .Range("O2:O" & lLastRow).EntireRow.Delete
        .ShowAllData
      End With
    End Sub

  10. The Following User Says Thank You to Maudibe For This Useful Post:

    HowardC (2013-10-20)

  11. #9
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,421
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Maudibe

    Thanks for you input

    Howard

Posting Permissions

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