Macro trouble finding 'empty' cells

  • Thread starter Thread starter foamfollower
  • Start date Start date
F

foamfollower

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
 
foamfollower said:
is there such a command line to 'find the first empty cell' (in a column)?
Click to expand...

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.
 
Back
Top