Appending new data to existing data in a spreadsheet

E

ExcelMonkey

I want to paste data into a sheet that already has data in it. I nee
to identify which cell to paste the new data into. I do not want t
over-write the existing data. I need to:

1) identify the first empty row
2) indentify the first empty column
3) pass these values to two separate variables
4) use these variables in an offset or address method to define th
paste cell for the data (i.e. if row 10 and column 2 are empty then th
cell address to paste to will be B10)

Does anyone know how to do this?

Thank-yo
 
M

mudraker

excelmonkey

here are 2 ways to get vacant row & column numbers.

Watch the auto word wraps.



RowNo = Range("a" & Rows.Count).End(xlUp).Row + 1
ColNo = Cells(1, Columns.Count).End(xlToLeft).Column + 1

GetBottomRow = TheSheet.Cells.Find(What:="*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row + 1

LastColumn = TheSheet.Cells.Find(What:="*", SearchOrder:=xlByColumns
_
SearchDirection:=xlPrevious).Column + 1

The following code gives 3 examples of using offset

sub TestOffset()
Dim GetBottomRow As Long
Dim LastColumn As Integer

GetBottomRow = Cells.Find(What:="*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

LastColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

Range("a1").Offset(GetBottomRow) = "hi"
Range("a1").Offset(GetBottomRow, LastColumn) = "abc"
Range("a1").Offset(0, LastColumn) = "welcome"
End Su
 
Top