I need a formula that will find the next empty cell within a column. There is
a formula in that cell but no data. The formula is the same through out the
column. What do you suggest?
--
Rick Rack

If you have a formula in the cell, then the cell is not empty.

If you're looking for the next cell that looks empty, maybe just looking down
that column...

Option Explicit
Sub testme()

Dim myCell As Range
Dim NextEmpty As Range

Set myCell = ActiveSheet.Range("a1")
Do
If myCell.Value = "" Then
Set NextEmpty = myCell
Exit Do
Else
Set myCell = myCell.Offset(1, 0)
End If
Loop

MsgBox NextEmpty.Address

End Sub

If the starting cell looks blank, then that'll be the nextcell. If you want it
to always be under the starting cell, change this line:

Set myCell = ActiveSheet.Range("a1")
to
Set myCell = ActiveSheet.Range("a1").offset(1,0)



Rick wrote:

I need a formula that will find the next empty cell within a column. There is
a formula in that cell but no data. The formula is the same through out the
column. What do you suggest?
--
Rick Rack

--

Dave Peterson

I use 'Selection.End(xlDown).Select' to position on the cell before the
next blank cell going downward.

I think you're looking for empty cells, though.

The original poster wanted to find cells that looked empty (formulas that
evaluate to "" are included).

wrote:

I use 'Selection.End(xlDown).Select' to position on the cell before the
next blank cell going downward.

--

Dave Peterson

Thanks Dave, it works great, now for another issue. Macro Build did the
following:________________________________________ ____________________
Sheets("Bald").Select
Range("A5:K12").Select
Selection.Sort Key1:=Range("A5"), Order1:=xlAscending, Key2:=Range("B5") _
, Order2:=xlAscending, Key3:=Range("C5"), Order3:=xlAscending, Header:=_
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom--
__________________________________________________ __________________
I now want to create sub procedure to do Sorts, but I have a problem that I
cannot get around, its the Range.Select statement. I have different length
sheets that I was to sort some are Range("B4:L24"), or Range("B5:L57")... Is
there a way to test the cell range ie ="B4:L24" or = "B5:L57" ?????


Rick Rack


"Dave Peterson" wrote:

If you have a formula in the cell, then the cell is not empty.

If you're looking for the next cell that looks empty, maybe just looking down
that column...

Option Explicit
Sub testme()

Dim myCell As Range
Dim NextEmpty As Range

Set myCell = ActiveSheet.Range("a1")
Do
If myCell.Value = "" Then
Set NextEmpty = myCell
Exit Do
Else
Set myCell = myCell.Offset(1, 0)
End If
Loop

MsgBox NextEmpty.Address

End Sub

If the starting cell looks blank, then that'll be the nextcell. If you want it
to always be under the starting cell, change this line:

Set myCell = ActiveSheet.Range("a1")
to
Set myCell = ActiveSheet.Range("a1").offset(1,0)



Rick wrote:

I need a formula that will find the next empty cell within a column. There is
a formula in that cell but no data. The formula is the same through out the
column. What do you suggest?
--
Rick Rack

--

Dave Peterson

How do you know if the range starts in row 4 or 5?
How do you know where the range starts?

Sometimes you can pick a column and use the last filled in cell in that column
to find the last row.

Dim myRng as range
dim LastRow as long
dim FirstRow as long
dim wks as worksheet

set wks = worksheets("bald")
with wks
lastrow = .cells(.rows.count,"B").end(xlup).row
firstrow = 5
set myrng = .range(.cells(firstrow,"B"),.cells(lastrow,"L"))
with myrng
.cells.sort key1:=.columns(1), order1:=xlascending, _
key2:=.columns(2), ....
end with
end with

But I don't have a guess how you would determine the first row.


Rick wrote:

Thanks Dave, it works great, now for another issue. Macro Build did the
following:________________________________________ ____________________
Sheets("Bald").Select
Range("A5:K12").Select
Selection.Sort Key1:=Range("A5"), Order1:=xlAscending, Key2:=Range("B5") _
, Order2:=xlAscending, Key3:=Range("C5"), Order3:=xlAscending, Header:=_
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom--
__________________________________________________ __________________
I now want to create sub procedure to do Sorts, but I have a problem that I
cannot get around, its the Range.Select statement. I have different length
sheets that I was to sort some are Range("B4:L24"), or Range("B5:L57")... Is
there a way to test the cell range ie ="B4:L24" or = "B5:L57" ?????


Rick Rack

"Dave Peterson" wrote:

If you have a formula in the cell, then the cell is not empty.

If you're looking for the next cell that looks empty, maybe just looking down
that column...

Option Explicit
Sub testme()

Dim myCell As Range
Dim NextEmpty As Range

Set myCell = ActiveSheet.Range("a1")
Do
If myCell.Value = "" Then
Set NextEmpty = myCell
Exit Do
Else
Set myCell = myCell.Offset(1, 0)
End If
Loop

MsgBox NextEmpty.Address

End Sub

If the starting cell looks blank, then that'll be the nextcell. If you want it
to always be under the starting cell, change this line:

Set myCell = ActiveSheet.Range("a1")
to
Set myCell = ActiveSheet.Range("a1").offset(1,0)



Rick wrote:

I need a formula that will find the next empty cell within a column. There is
a formula in that cell but no data. The formula is the same through out the
column. What do you suggest?
--
Rick Rack

--

Dave Peterson


--

Dave Peterson