Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Macro to Select (Excel 2003 SP2)

    I would like to be able to highlight a selection of formulas in a column and then execute a macro that can:
    1) detect cells with zero value in the selection,
    2) select the rows of the cells with the zero values and then
    3) highlight the rows.
    Also, how would the macro be modified so that the rows could be hidden or deleted, instead of just highlighting them?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to Select (Excel 2003 SP2)

    Try this:

    Sub HandleZeros()
    Dim rng As Range
    Dim r As Long
    Set rng = Selection.Columns(1)
    For r = rng.Rows.Count To 1 Step -1
    If Not rng.CellsŪ = "" And rng.CellsŪ = 0 Then
    rng.CellsŪ.EntireRow.Interior.ColorIndex = 6
    'rng.CellsŪ.EntireRow.Hidden = True
    'rng.CellsŪ.EntireRow.Delete
    End If
    Next r
    End Sub

    Comment out the line

    rng.CellsŪ.EntireRow.Interior.ColorIndex = 6

    and uncomment one of the lines below it to change the action of the macro.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro to Select (Excel 2003 SP2)

    Thank You Hans. Works great!

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro to Select (Excel 2003 SP2)

    Hans, Thanks again for helping yesterday.
    But now you've got me thinking, "suppose I need to find a text string instead of a zero?".
    Looks to me like I would have to modify this statement from what you showed me earlier:
    If Not rng.CellsŪ = "" And rng.CellsŪ = 0 Then
    Would I simply replace the = 0 with = "Total"? (Where "Total" or some other text would be my search criteria)

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to Select (Excel 2003 SP2)

    The zero was a special case because Excel considers a blank cell to have a value 0. To search for a specific text string, change

    If Not rng.CellsŪ = "" And rng.CellsŪ = 0 Then

    to

    If rng.CellsŪ = "Total" Then

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Macro to Select (Excel 2003 SP2)

    The following code has proved very helpful and I now have a use for a variation of the code.
    I would like to modify the following code to select (or delete) Greater than -100 AND Less than 100.

    Selection.EntireRow.Hidden = False
    Dim rng As Range
    Dim r As Long
    Set rng = Selection.Columns(1)
    For r = rng.Rows.Count To 1 Step -1
    If Not rng.CellsŪ = "" And rng.CellsŪ < 100 Then
    rng.CellsŪ.EntireRow.Delete
    End If
    Next r


    Can I adjust the code
    from: If Not rng.CellsŪ = "" And rng.CellsŪ < 100 Then
    to: If Not rng.CellsŪ = "" And rng.CellsŪ >-100 and <100 Then

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to Select (Excel 2003 SP2)

    You must use a complete condition for each one. Try

    If Not rng.CellsŪ = "" And rng.CellsŪ < 100 And rng.CellsŪ > -100 Then

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro to Select (Excel 2003 SP2)

    Works like a charm. Thank you Hans.

Posting Permissions

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