Selecting first empty cell in a list 1

[duncansancho]

Hello all,
I have an application which produces lists of various lengths in excel. As each new entry is generated by the macro it should be entered under the last in a column. How please!
Duncan

 

[AgatheB]

'First find the last USED row on the sheet

With ActiveSheet
Last_row_sheet = .UsedRange.Row + .UsedRange.Rows.Count - 1

' Now loop up to find the first cell with a value.
For R = Last_row_sheet To 1 Step -1
If Not IsEmpty(.Cells(I, Column_to_search)) Then
The_LastRow = R
Exit For
End If
Next
End With

 

[Bowers74]

Range("A65536").End(xlUp).Offset(1,0)

Will get you to the first empty cell at the bottom of the cells in Column A of the active sheet.



Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[AgatheB]

BTW Duncan,

Look at the Tab with Frequently Asked Questions.

Regards, Agathe

 

[duncansancho]

Many thanks to you both,
Duncan

 

[SkipVought]

Duncan,

If you want to be a pro...

Dynamic Lists ought to have dynamic NAMED RANGES to be of the most usefulness. faq68-1331 How can I rename a table as it changes size


Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[duncansancho]

Thanks Skip!
Duncan

 

[SkipVought]

BTW,

I failed to state that using the OFFSET function in Insert/Name/Define is one of the best ways to define a range dynamically!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com