Hello,
Today is just one of those days that Excel just keeps beating my butt. After an hour of trying to write a simple three line code and getting nowhere I have come to the experts.
The background is that I have a string in cell A10, ten blank cells (going down the column), and then a string in cell A21.
What I'm trying to do is Select A10 and all the blank cells in A2:A20. Do a fill down (Cntl-D) then moving to the next cell down.
I can do this using Record Macro by.
Cell A10 is the active cell
Range(Selection, Selection.End(xlDown).Select
With A10 still the active cell I hold down the Shft Key and press the End key then press the DownArrow key.
This selects all the cells from A10 to A20. This includes A20 - which has a value already in it.
Since I only want cells A10 through A19 selected I continue holding down Shft Key and then press the UpArrow key once.
This leaves A10 through A19 selected.
Now to copy the value in A10 down through A19 I press the Ctrl and DownArrow keys.
This results in all cells between A10 and through A19 have the same value.
All of the above steps are done in the Macro Recorder, resulting in the VBA code of:
Range(Selection, Selection.End(xlDown)).Select
Range("A10:A19").Select
Application.CutCopyMode = False
Selection.FillDown
Since I want to do this process multiple times in the spreadsheet with different cell addresses, this code doesn't work because the UpArrow key is translated into an actual cell address.
I've tried replacing the second line with ActiveCell.Offset(-1,0) to move up one cell. Well, it does move up one cell, but to one cell above A10, i.e. A9 and turns the selection process.
Any help is most welcome.
Thanks
George Teachman
Today is just one of those days that Excel just keeps beating my butt. After an hour of trying to write a simple three line code and getting nowhere I have come to the experts.
The background is that I have a string in cell A10, ten blank cells (going down the column), and then a string in cell A21.
What I'm trying to do is Select A10 and all the blank cells in A2:A20. Do a fill down (Cntl-D) then moving to the next cell down.
I can do this using Record Macro by.
Cell A10 is the active cell
Range(Selection, Selection.End(xlDown).Select
With A10 still the active cell I hold down the Shft Key and press the End key then press the DownArrow key.
This selects all the cells from A10 to A20. This includes A20 - which has a value already in it.
Since I only want cells A10 through A19 selected I continue holding down Shft Key and then press the UpArrow key once.
This leaves A10 through A19 selected.
Now to copy the value in A10 down through A19 I press the Ctrl and DownArrow keys.
This results in all cells between A10 and through A19 have the same value.
All of the above steps are done in the Macro Recorder, resulting in the VBA code of:
Range(Selection, Selection.End(xlDown)).Select
Range("A10:A19").Select
Application.CutCopyMode = False
Selection.FillDown
Since I want to do this process multiple times in the spreadsheet with different cell addresses, this code doesn't work because the UpArrow key is translated into an actual cell address.
I've tried replacing the second line with ActiveCell.Offset(-1,0) to move up one cell. Well, it does move up one cell, but to one cell above A10, i.e. A9 and turns the selection process.
Any help is most welcome.
Thanks
George Teachman