Selecting next blank cell - Page 2
Selecting next blank cell
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 34

Thread: Selecting next blank cell

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

    Default

     
    Thats great but is it possible to only put the x in the next blank cell not them all

    [ This Message was edited by: Yellowdude on 2002-03-05 12:42 ]

  2. #12
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-05 12:42, Yellowdude wrote:
    Thats great but is it possible to only put the x in the next blank cell not them all

    [ This Message was edited by: Yellowdude on 2002-03-05 12:42 ]
    Define "next". Describe your table.

  3. #13
    New Member
    Join Date
    Mar 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ive got a table 10 x 10 which is blank .
    I want to create a marco which will got through the table and place an x in the next empty cell. It would go through row A then B etc untill if finds and empty cell and fills it in

    [ This Message was edited by: Yellowdude on 2002-03-05 13:02 ]

  4. #14
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Row "A" or column "A"?

  5. #15
    Guest

    Default

    On 2002-03-05 13:26, Mark W. wrote:
    Row "A" or column "A"?
    soor i meant column A

  6. #16
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Okay, here's where you need a VBA guru...

    Just loop through each sucessive column and perform...

    Selection.SpecialCells(xlCellTypeBlanks).Select

    until you find a blank cell and then enter "x" into the active cell.

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

    Default

    Try this macro, it starts in column A and places an "X" in the next available row of columns A:J, up to and icluding row 11. I coded for row 11 because you said the table is 10 X 10, and I thought that may mean row 1 is a header and rows 2:11 are the 10 rows of data. If I guessed incorrectly, please modify accordingly.

    Sub X_MarksTheSpot()
    Application.ScreenUpdating = False
    [A1].Activate
    Do Until ActiveCell.Column = 11
    If ActiveCell.Offset(1, 0) = "" Then
    ActiveCell.Offset(1, 0) = "X"
    ElseIf ActiveCell.End(xlDown).Offset(1, 0).Row > 11 Then
    ActiveCell.Offset(0, 1).Activate
    Else
    ActiveCell.End(xlDown).Offset(1, 0) = "X"
    ActiveCell.Offset(0, 1).Activate
    End If
    Loop
    [A1].Select
    Application.ScreenUpdating = True
    End Sub

    Any help?

    Tom Urtis

  8. #18
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tom, can't this be done with fewer conditions and using Selection.SpecialCells(xlCellTypeBlanks).Select?


    [ This Message was edited by: Mark W. on 2002-03-05 13:38 ]

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

    Default

    Hey Mark,

    My understanding of the posted string was that he only wanted an "X" in the first blank cell of each column, not all blank cells in each column.

    The code could have been shorter (and still could be modified as such) with fewer conditions if he wants an X in the first available cell from the bottom (row 11) up. But he intimated from the the top down. So, if rows 2:11 were populated with Jack, Bill, Bob, "", "", Tom, Mike, Jim, "", "", my suggestion will place an X in row 5 (the first ""), which is what I think he asked for.

    I'm open for suggestions though, so let me know what you think.

    Tom

    Addendum, what I thought should be taken into account is every possibility: Nothing in row 2, a blank cell between 2:11, or no blank cell. The End, Down, and Offset references, as I understand them, needed to take those possibilities into account.

    T.U.

    [ This Message was edited by: Tom Urtis on 2002-03-05 13:55 ]

  10. #20
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Right, but why not use "Selection.SpecialCells(xlCellTypeBlanks).Select" to find the empty cell instead of testing cell by cell. Once you've selected the cell(s) then you restrict the selection to the active cell produced by "Selection.SpecialCells(xlCellTypeBlanks).Select".

    Wouldn't this algorithm run faster, or am I mistaken to believe that using Excel's native commands are optimized and; therefore, preferable worksheet navigation.

    [ This Message was edited by: Mark W. on 2002-03-05 15:02 ]

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
  •  

 

 
DMCA.com