Is there a simple way to find the first empty cell in a column.
I know that Selection.End(xlDown).Select will get me the last one with something in it. Is there something i can add to get to the next cell
Thanks
Is there a simple way to find the first empty cell in a column.
I know that Selection.End(xlDown).Select will get me the last one with something in it. Is there something i can add to get to the next cell
Thanks
Function lastRowpub(colnum As Long, Optional Sh As Worksheet) As Long
' Count Rows in table
If Sh Is Nothing Then Set Sh = ActiveSheet
lastRowpub = Sh.Cells(Sh.Rows.Count, colnum).End(xlUp).Row
End Function
Sub test()
ilastrow = lastRowpub(1, Worksheets("sheet1")) + 1 ' (row number,worksheet name)
Range("a" & ilastrow).Activate ' activate the last row in the column
End Sub
If you had data in column A on Sheet1 this code will find the last cell then increase by 1 and then activate the last cell in column A
How do i call this function? I have a macro with a sub() end sub, but it wont let me put the function in between. If i put it separate i get a "subscript out of range" error
MIncorporated - This is another way.
flyingmeatball - The Function is separate. Look again at funkymonkUK's sub test() to see how you call a Function.Please Login or Register to view this content.
Both of the previous solutions will yield the LAST empty cell in a column. If indeed, what you need is for the FIRST empty cell in a column, then use:
Range("a1").End(xlDown).Offset(1,0)
assuming your data is in Column A with or without embedded empty cells.
HTH
Myles
Thanks, the offset(1,0) was what i was looking for, ended up just using a loop to do it tho.
MInc.,
There is no need to loop. The single line code
Sub PickFirstEmptyCellInColA()
Range("a1").End(xlDown).Offset(1,0)
End sub
just suffices.
HTH
Myles
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks