Creating a macro that doesn't over write cells

Marcia

New Member
Joined
Sep 24, 2002
Messages
4
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!
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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

New Member
Joined
Sep 24, 2002
Messages
4
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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

New Member
Joined
Sep 24, 2002
Messages
4
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,396
Members
414,235
Latest member
sucosama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top