finding next empty cell using macro ..

devonnicious · #1 (**permalink**) 08-26-04, 04:24

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 !

MikeTheBike · #2 (**permalink**) 08-26-04, 08:32

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

DavidCoutts · #3 (**permalink**) 08-26-04, 09:41

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

DavidCoutts · #4 (**permalink**) 08-26-04, 09:46

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

Internet Services

Web Development

Digital Marketing

Consumer Tech