Results 1 to 10 of 10
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Delete everything in selected cells not 123

    I would like to run a macro that would delete all text, symbols and other numbers other than the number "123". Any help?Thanks in advance.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    JL,

    The following will work with one caveat, you must have the active cell of the range be the upper left cell.
    Code:
    Option Explicit
    
    Sub DeleteInRange()
    
       Dim rngActive As Range
       Dim lColCnt   As Long
       Dim lRowCnt   As Long
       Dim lRowCntr  As Long
       Dim lColCntr  As Long
       
    
       Set rngActive = Range(Selection.Address)
       lColCnt = rngActive.Columns.Count - 1
       lRowCnt = rngActive.Rows.Count - 1
       
       
         For lRowCntr = 0 To lRowCnt
            For lColCntr = 0 To lColCnt
            If ActiveCell.Offset(lRowCntr, lColCntr).Value <> 123 Then ActiveCell.Offset(lRowCntr, lColCntr).ClearContents
            Next lColCntr
         Next lRowCntr
       
    End Sub
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Geek. 2 things: first, what do you mean by "you must have the active cell of the range be the upper left cell. "?; and second, in actuality, instead of the number being "123", it would be a 6 digit number that would begin with "123". Irt might be "123456", 123890", etc.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    JL,

    By Upper Left Cell I mean:
    RangeActiveCell.JPG
    In this example A1 is the upper left cell {active cell}.

    In the second case change the test to:

    If ActiveCell.Offset(lRowCntr, lColCntr).Value < 123000 And _
    ActiveCell.Offset(lRowcntr, lColCntr).Value > 123999 Then _
    ActiveCell.Offset(lRowCntr, lColCntr).ClearContents
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    You may care to play around with this User Defined Function JustNumerics

    Open the VB environment (ALT F11) and insert a new Module in which you put the following code:

    Code:
    Function JustNumerics(ByVal Rng As String)
    Dim x As Object
    Set x = CreateObject("VBScript.RegExp")
    With x
        .Pattern = "\D"
        .Global = True
        JustNumerics = .Replace(Rng, "")
    End With
    End Function
    Then, on your Worksheet, try out the Function by typing this formula in a cell =JustNumerics(A1) or whatever cell you want to transform.

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Martin. How would I do a range?

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Please can you give a precise description of what you mean by "do a range" ?

    Attaching an example workbook (sanitised data), showing what you expect before and after, can save a lot of misunderstanding.

  8. The Following User Says Thank You to MartinM For This Useful Post:

    zeddy (2012-02-14)

  9. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Martin

    Terrific function. Got any more?

    zeddy

  10. #9
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I found something similar to that one when investigating the VBScript.RegExp expression, which enables some unique operations, and adapted it.

    I think you've already seen CellAbove, which I got from this Forum. Excel can be wayward in how it extends a Sum or Subtotal's range depending on where and how you insert rows: using CellAbove ensures you always have the correct range. It is defined as the Name CellAbove with the following "Refers to:" and is then used directly in formulae such as SUM(A1:CellAbove).

    Code:
    =INDIRECT("R[-1]C",0)
    Again from this Forum, a contributor kindly helped me with a SubtotalIF function, which exactly mirrors the SumIF function and I find very useful indeed:

    Code:
    Function SubTotalIF(rngLabels As Range, Criterion As Variant, rngValues As Range)
    Dim Subt As Double
    Dim rcell As Range
    Dim vcell As Range
    Dim n As Long
    Application.Volatile
    n = 1
    For Each rcell In rngLabels
    If Left(rngValues.Cells(n).Formula, 10) = "=SUBTOTAL(" _
    And UCase(rcell) = UCase(Criterion) Then
    Subt = Subt + rngValues.Cells(n)
    End If
    n = n + 1
    Next rcell
    SubTotalIF = WorksheetFunction.SumIf(rngLabels, Criterion, rngValues) - Subt
    End Function
    Finally Price which was discussed at length here with yourself: difficult to describe, but again I find very useful. This one returns the value in the cell to the right of the cell whose Range Name appears in the cell which is input into the UDF !

    Maybe a better way of describing it is this: I have several tables of Financial Assets, each of which has a verbose Range Name to make it easy to enter consistently anywhere in the Workbook. The next column in the tables always contains the up-to-date price for each Asset. Using the Price UDF I can lookup the price associated with any Asset in a formula with Price(the cell which is showing the Asset name). I could have used VLookup but this way is more intuitive when building, reviewing and maintaining a very large Workbook and more flexible - it allows some prices to be kept in several places for instance.

    Code:
    Function Price(Asset As Range)
    Application.Volatile
    Price = Range(Mid(Asset.Cells(1).Formula, 2)).Offset(0, 1).Value
    End Function
    Last edited by MartinM; 2012-02-14 at 12:20.

  11. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    many thanks Martin.

    I can now see why you use these functions.

    zeddy

Posting Permissions

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