Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Using a macro to select multiple cells

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm trying to get a macro to add a row to the selection if the cell in column G has a zero in it. Is there any way of adding to the selection on a row-by-row basis like you can with sheets using the 'Replace' parameter?

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Please clarify, do you mean if the cell contains a single "0", or the word "zero", or a number containing a 0, such as 1025?

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    I mean the number zero, "0".

  4. #4
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    See if this macro does what you are requesting.

    Sub AddZeroRow()
    Application.ScreenUpdating = False
    [G2].Select
    Do Until Selection.Value = ""
    If Selection.Value = "0" Then
    Selection.Insert Shift:=xlDown
    ActiveCell.Offset(2, 0).Select
    Else
    ActiveCell.Offset(1, 0).Select
    End If
    Loop
    [G1].Select
    Application.ScreenUpdating = True
    End Sub

    Any help?

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry, I obviously didn't make myself very clear. What I want to do is select multiple rows that are not next to each other, the equivalent of:

    Range("9:9,12:12,14:14,19:19,23:23").Select

    ...where only rows with 0 in column G are selected. With sheets you can use

    sheets.("MySheet").select(False)

    to add to collection of selected sheets, but this doesn't work with range.select.

    Is there anyway you can do this?

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Johnny

    There may be an easier way, but try this:

    Sub multiSelectRows()
    Dim stmySelect() As String 'Array to store row numbers of found cells
    Dim stLastSelect As String 'Variable to store last row number whilst comma stripped away
    Dim stFullSelect As String 'Variable to hold full string of row numbers
    Dim x As Integer
    x = -1
    With Worksheets("Sheet1")
    For Each c In Columns(7).Cells
    If c = "0" Then
    x = x + 1
    ReDim Preserve stmySelect(x)
    stmySelect(x) = c.Row & ":" & c.Row & ","
    Else
    End If
    Next c
    stLastSelect = Left(stmySelect(x), Len(stmySelect(x)) - 1)
    ReDim Preserve stmySelect(x - 1)
    ReDim Preserve stmySelect(x)
    stmySelect(x) = stLastSelect
    stFullSelect = ""
    For x = 0 To x
    stFullSelect = stFullSelect & stmySelect(x)
    Next x
    .Range(stFullSelect).Select
    .[A1].Activate
    End With


    End Sub

    I don't know whether you need to check all the cells, if not just amend the range.

    Any help?

    Regards

    Robb__

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    That should do it. Thanks Robb.

    You would have thought Excel would have made this easier to do...

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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