Creating a macro that doesn't over write cells

[Marcia]

My problem is that I want to transport data downloaded daily into excel in list form to several work sheets. I can do this but when the next day data is entered and the macro run, yesterday's data is over written. Also if a blank entry is made I need excel to recognize that this is the last cell and need the data entered there. So basically I want excel to transport the list of data to the appropriate work sheet and to the furthest right empty cell in a row that I specify. The "last cell" function isn't doing this for me as it stays in the row and skips to the last column on the whole spreadsheet that was used, even if not used in that row...How do I get this to work???
-Thanks!

 

[Damon Ostrander]

Hi Marcia,

To find the first completely unused row at the end of a worksheet named X:

With Worksheets("X")
iRow = UsedRange.Row + UsedRange.Rows.Count
End With

One caveat with this: UsedRange is the range that contains data OR cell formatting, so an empty but formatted cell including cells whose contents have been cleared will still be included in UsedRange. If you don't want these cells included (i.e., if your worksheet is not "pristine"), then you should use the End property. For example to find the row of next cell after the last cell containing data in column B:

iRow = Worksheets("X").[B65536].End(xlUp).Row + 1

Regarding your question about the furthest right empty cell in a row of your choice (iRow in this example), this cell is:

Worksheets("X").Cells(iRow,256).End(xlToLeft).Offset(0,1)

This is the cell itself (a Range object). To get the column of this cell:

Worksheets("X").Cells(iRow,256).End(xlToLeft).Column + 1

 

[Marcia]

Unfortunately I don't know where I'm to put this code in. I took a C++ course a few years ago but that is the extent of my programming knowledge. Does this bit that you sent me go into the formula window in excel or do I have to insert it somewhere into Visual Basic??
I think the big problem is that when using a macro, the specific cell is selected rather than the action of going to the last blank cell in the row. My chart is not pristine as zero values are set to blanks, and these blanks are to be filled upon a non zero value entry.
All I want is excel to take this data, put it into the right work sheet, at the end (last free cell) of the appropriate row, and not over write it when I want to use this macro again.

On 2002-09-25 10:40, Damon Ostrander wrote:
Hi Marcia,

To find the first completely unused row at the end of a worksheet named X:

With Worksheets("X")
iRow = UsedRange.Row + UsedRange.Rows.Count
End With

One caveat with this: UsedRange is the range that contains data OR cell formatting, so an empty but formatted cell including cells whose contents have been cleared will still be included in UsedRange. If you don't want these cells included (i.e., if your worksheet is not "pristine"), then you should use the End property. For example to find the row of next cell after the last cell containing data in column B:

iRow = Worksheets("X").[B65536].End(xlUp).Row + 1

Regarding your question about the furthest right empty cell in a row of your choice (iRow in this example), this cell is:

Worksheets("X").Cells(iRow,256).End(xlToLeft).Offset(0,1)

This is the cell itself (a Range object). To get the column of this cell:

Worksheets("X").Cells(iRow,256).End(xlToLeft).Column + 1

 

[Damon Ostrander]

Hi Marcia,

The code I provided is VBA code since you indicated you were working with a VBA macro. It is hard to describe to you how to use this code without knowing what your existing code looks like. If you would like to post your code or email it to me I could perhaps incorporate it for you.

Damon

mailto:VBAexpert@piadamon.com

 

[Marcia]

Here is the code from my basic recorded macro. It is simply copying from one cell and pasting into another that happens to be the last available cell in the row. It can easily be seen that the cell to be pasted in is specified rather than the act of putting it in the last available cell...
I should really learn some VB...
Do you think you can help??
-Marcia

Selection.Copy
Sheets("MSGDUST").Select
Range("B236").Select
Selection.End(xlToRight).Select
Range("D236").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("A2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("MSGDUST").Select
ActiveWindow.SmallScroll Down:=-15
Range("B216").Select
Selection.End(xlToRight).Select
Range("N216").Select
ActiveSheet.Paste