Delia has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
Excel VBA Intro Intermediate course
Macro recording
I want to use a macro to copy data down to fill in all the blank cells in the column below the data until there is a cell with data in in and then move down to the cell with data in it to copy the procedure.
When recording the macro I selected the range to copy to by using the shift+end+ the down arrow key, then went back up a cell. When I used the macro to copy the next entry down the macro only copied the same number of lines, and did not fill in all the blank cells.
How do I get it to fill all the blank cells?
RE: Macro recording
Delia
Whenever I get a problem with a Marco that does not work out as I want it to, the first thing I think about is breaking the result down into a step by step process, and then going through it manually, as if I was going to be recording the macro.
From what you have described, Try the following:
Before you start recording the Macro, select ONE cell that contains the data you want to copy. (You can only select multiple cells if the destination is exactly the same number of cells or a multiple of the selected ones). Selecting one keeps it simple for the moment.
1. Start recording the maco. Ensure you click on the RELATIVE REFERENCING button next to the STOP button on the Macro toolbar. (This ensures that Excel always selects the cell you choose prior to running the macro, NOT an absolute cell - ie. not $A$4).
2. COPY the Cell using CTRL+C
3. Navigate to the empty cell using the keyboard.
- (END+DOWN, then DOWN key again).
4. Navigate to the empty cell using the keyboard.
- (SHIFT+END+DOWN key, then holding SHIFT, the UP key ONCE).
5. Paste the copied cell using CTRL+V
6. Click once on the ORIGINAL cell you selected and copied. (This will loop the macro, so when you run it again, excel will start at step 2, and run the macro again.
DONE.
Let me know how you do. It may take some testing. You have to think for and like Excel to get it right. Adding step 6 in makes the whole thing work very fast. Once you have the correct result in the macro, you can just hold down the shortcut key and Excel will do all your work for you.
Richard
