Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    18

    Unanswered: finding next empty cell using macro ..

    Hi,
    may i know what are the functions/codes for macros, to find the next empty cell in a specified column? and i would also like to know ..

    after ive pasted some data in the excel using macro, the activecell (lets say) will be C74. and i wish to use macro to choose the cell below the active cell. which will be C75.

    how do i go about doing these?

    thank you very much !

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    This code move the selection to the last occupied cell that has continuous data and then moves to the next cell down (the first cell without data).
    Code:
    Sub Macro1()
    
        Selection.End(xlDown).Select
        With ActiveCell
            Cells(.Row + 1, .Column).Select
        End With
    End Sub
    Hope this is wat you had in mind

    MTB

  3. #3
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Just in addition to what mike said you can also look at this
    Code:
        ' find the first blank cell in a range
        Range("A1:A10").Find("", ActiveCell, , , , SearchDirection:=xlNext).Select
        'find the first filled cell in a range
        Range("A1:A10").Find("*", ActiveCell, , , , SearchDirection:=xlNext).Select
        'find the last blank cell in a range
        Range("A1:A10").Find("", ActiveCell, , , , SearchDirection:=xlPrevious).Select
        'or Find the last filled cell in a range
        Range("A1:A10").Find("*", ActiveCell, , , , SearchDirection:=xlPrevious).Select
        
        'moving
        ActiveCell.Offset(1, 0).Select
    but try to avoid activecell and selecting as much as possible
    HTH
    David

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    oh and i forgot one thing to select all blank cells in a range you can use

    Range("A1:A10").SpecialCells(xlCellTypeBlanks).Sel ect
    or constants
    Range("A1:A10").SpecialCells(xlCellTypeConstants). Select

Posting Permissions

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