Keystoke Macro

[donh]

I am trying to get a macro that will work off of keystrokes.

Does anyone know how to write a macro for this?

at any cell:
copy
down arrow
shift, control, down arrow
shift, up arrow
paste
control, down arrow

If there is data in rows 4,12,14,18 and titles in row 1 every time I try this, the range is constant Range("A4:A12")

This defeats the purpose of "control, down arrow" to find the next row with data. If the next row that has data is row 14 this macro will overwrite it because the range is 8 rows.

I am trying to fill in rows with data where the data is at varying rows and the data is different at each row. The data comes to me different each time (the next time say rows
7,11,20,25) and is usually over 1,000 rows deep.

I also would like to know how to do it with the up arrow too.

at any cell:
copy
up arrow
shift, control, up arrow
shift, down arrow
paste
control, down arrow
control, down arrow

Does anyone have any ideas?

[MrExcel]

The answer will be in the .End method.

ActiveCell.End(xlDown) will be like your Ctrl+Shift+DownArrow

ActiveCell.End(xlUp) will be like your Ctrl+Shirt+UpArrow

If you start in A4, and need to go down a row, then this will do it:
ActiveCell.Offset(1, 0).Select

You can also specify a range by specifying the first and last cells of the range. This bit of code will select the blank cells:
Range(ActiveCell.Offset(1, 0), ActiveCell.offset(1, 0).End(xldown).offset(-1, 0)).Select

Bill

[MrExcel]

You will also find that what you are trying to do is similar to filling in the blank cells in a pivot table. Follow the instructions in this tip of the week to do this quickly through the user interface:
http://www.mrexcel.com/tip004.shtml

You can also automate this with the following VBA:

    Range("A4:A100").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    Range("A4:A100").Copy
    Range("A4:A100").PasteSpecial xlPasteValues

Bill

_________________
MrExcel.com Consulting

[ This Message was edited by: MrExcel on 2002-05-08 20:22 ]

[NateO]

Very nice, MrExcel lends a hand. Since we're on the same thread, I'll add: excellent board sir (kudos). If nobody minds, I'll add to the foray:

Donh, I swear I'm not leaving you out to dry, I just needed a break after a long day at the office, some Maple Leaf Hockey and a cold beverage (er two). In any case the code at the following post has some serious merits for what you want to accomplish:

http://www.mrexcel.com/board/viewtop...c=7672&forum=2

I didn't write it as straight forward as possible, and for that I apologize. I'm trying to demonstrate how to select cells and ranges without [actually] selecting cells, making it more difficult to interpret but worthwile for you to review.

But allow me to break it down (to the nitty gritty), with selections (you don't need this but to duplicate key strokes). To move down:

activecell.offset(1,0).select

Offset has two parameters row, then range (e.g., if you want to offset the active cell one row: offset(1,0), one column: offset (0,1) ).

To move up, or right, make the offset negative. E.g., one row up is:

activecell.offset(-1,0).select

.

Two more points of interest. To select a multi-cell ranges in VBA, you can use:

Range(range1:range2).select

This will select every cell between the two ranges, as I used in the earlier post.

The other code implimented is the cell(row numer, column number). This allows you to work with row numbers and column numbers (whereas columns are typically defined by a letter).

Your data sheet is tough to interpret from afar, hopefullly between this explanation and an earlier example of these techniques it will help you progress towards success.

Otherwise, post back. But keep posting under the same post, so that people know what's out there and how to build from there (instead of scratch).

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-08 20:40 ]

[NateO]

Alrighty, no disrespect to the founder of this website meant with my post, I was typing while MrExcel posted (is my post long enough?). Also, review the pivot table possibility, after reviewing your array of data, it may be much easier to consolidate as needed. Incidentally, you can record pivot table set-ups as a macro. Good hunting.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-08 20:41 ]

[donh]

WOW!!!

The “filling in the blanks tip” from MrExcel has to be the slickest thing I have ever seen. I had 3 spreadsheets that all were over 52,000 rows deep. I needed to fill at least 50,000 of those rows x 2 columns x 3 spreadsheets and in a manner of seconds over 300,000 rows were filled.

With a little bit of thinking I was able to get the tip to fill “up” as needed.

I am forever indebted.

Thanks