Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro trouble finding 'empty' cells
Hi,
i forgot i had this little problem. i'm trying to paste data to a specific column, for example- F253. the problem is, it may not be F253 every time (only now they tell me) what i think would work is to put a line in before the Paste that finds the first empty cell in the F column. since there is no data anywhere underneath, seems possible. is there such a command line to 'find the first empty cell'? Thanks for any input, Have a great day! SF |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro trouble finding 'empty' cells
"foamfollower" wrote in message
om... is there such a command line to 'find the first empty cell' (in a column)? This illustrates two methods. The first fails if the column is empty or if there is data in the last cell. The second finds the second cell if the column is empty. It also finds some cell further up the column if the last cell is not empty. If your column always has data in it and never nears the bottom row, these will work as is. Otherwise you may need to add tests for special cases. Sub findit() Dim r As Range '1) Use Find. Set r = Columns("F").Find(What:="*", _ SearchDirection:=xlPrevious, _ SearchOrder:=xlByRows)(2) Application.Goto r MsgBox r.AddressLocal '2) Use End Set r = Columns("F").Cells(Rows.Count).End(xlUp).Offset(1, 0) Application.Goto r MsgBox r.AddressLocal End Sub Note that the unqualified Column("F") refers to column F in the active sheet, if it exists. Use Workbooks("Book1.xls").Worksheets("Sheet1").Column ("F"). etc. or equivalent to specify a particular worksheet and workbook. Also, look up Last Cell on Google. This is last-cell (or first empty cell) thing is a common concern. -- Bob Kilmer "foamfollower" wrote in message om... is there such a command line to 'find the first empty cell'? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to clean empty cells | Excel Discussion (Misc queries) | |||
Finding data amongst empty cells | Excel Discussion (Misc queries) | |||
Finding days between cells C and L where the cells may be empty | Excel Worksheet Functions | |||
Macro to copy value in empty cells | Excel Discussion (Misc queries) | |||
macro to delete empty cells | Excel Programming |