last non-empthy cell

[Guest]

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]

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.

 

[Tom Ogilvy]

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

 

[Dave Ramage]

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

 

[Guest]

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:

 

[Tom Ogilvy]

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