last non-empthy cell
 

hi, i need to "tell" the macro to move the coursor to the first empthy cell
in a coloumn, how to do that?

thanks, dov

last non-empthy cell
 

One way is to look at the top cell. then the second, then slide down to the
bottom if both are non-empty:

Option Explicit
Sub testme()

Dim DestCell As Range
Dim iCol As Long

iCol = 6

With Worksheets("sheet1")
If IsEmpty(.Cells(1, iCol)) Then
Set DestCell = .Cells(1, iCol)
ElseIf IsEmpty(.Cells(2, iCol)) Then
Set DestCell = .Cells(2, iCol)
Else
Set DestCell = .Cells(1, iCol).End(xlDown).Offset(1, 0)
End If
End With

DestCell.Select

End Sub

I used iCol = 6 (column F) in my example.

dov wrote:

hi, i need to "tell" the macro to move the coursor to the first empthy cell
in a coloumn, how to do that?

thanks, dov

--

Dave Peterson

last non-empthy cell
 

Dim rng as Range
on Error resume Next
set rng = columns(3).SpecialCells(xlBlanks)(1)
On Error goto 0
if rng is nothing then
set rng = cells(rows.count,1).End(xlup)
End if
msgbox "Next empty cell is " & rng.Address

--
Regards,
Tom Ogilvy


"dov" wrote in message
...
hi, i need to "tell" the macro to move the coursor to the first empthy
cell
in a coloumn, how to do that?

thanks, dov

last non-empthy cell
 

Dov,

To simply select the cell:
ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End
(xlUp).Offset(1,0).Select

Or, to get the row number:

Dim lRow as Long
lRow = ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End
(xlUp).Row + 1
Range("A" & lRow).Value = "Next Cell"

Cheers,
Dave
-----Original Message-----
hi, i need to "tell" the macro to move the coursor to the
first empthy cell
in a coloumn, how to do that?

thanks, dov

.

last non-empthy cell
 

Perhaps this function will do:

lastrow = lastcell(worksheetname)
Cells(lastcell, colnum).Select

Function lastcell(wsname)
Sheets(wsname).Select
lastcell = Application.WorksheetFunction.CountA(Columns("A:A" ))
End Function

Hi, Stefi


€ždov€� ezt Ã*rta:

hi, i need to "tell" the macro to move the coursor to the first empthy cell
in a coloumn, how to do that?

thanks, dov

last non-empthy cell
 

Need another line:

Dim rng as Range
on Error resume Next
set rng = columns(3).SpecialCells(xlBlanks)(1)
On Error goto 0
if rng is nothing then
set rng = cells(rows.count,1).End(xlup)
End if
if not isempty(rng) then set rng = rng.offset(1,0)
msgbox "Next empty cell is " & rng.Address

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
Dim rng as Range
on Error resume Next
set rng = columns(3).SpecialCells(xlBlanks)(1)
On Error goto 0
if rng is nothing then
set rng = cells(rows.count,1).End(xlup)
End if
msgbox "Next empty cell is " & rng.Address

--
Regards,
Tom Ogilvy


"dov" wrote in message
...
hi, i need to "tell" the macro to move the coursor to the first empthy
cell
in a coloumn, how to do that?

thanks, dov