In followup to this thread: http://www.mrexcel.com/forum/showthread.php?t=427424
I have a hopefully simple question.
Background: I have a set of data that is exported the same. The only difference is that there may be varying rows worth of data (A1:G5, A7:G11 vs A1:G3, A5:G7, A1:G10, A12:G21), but they will always fill cells A:G. I want to transform the data horizontally so I can import to Access.
Excel 2003<br>
Excel 2003
Using the above Macro on this data set
Excel 2003
results in:
Excel 2003
Yet this code:
results in both of these, which is what I want
Excel 2003
and
Excel 2003
So, to my question.
I was advised to avoid using SendKey, and would like some basic help with the syntax for using the arrow keys and modifiers.
Selection.End(xlDown).Select = Ctrl+DownArrow
Range(Selection, Selection.End(xlDown)).Select = Ctrl+Shift+DownArrow
What is the syntax for Shift+LeftArrow
And is there a way to loop this code: Selection.End(xlDown).Select
So instead of
Selection.End(xlDown).Select
Selection.End(xlDown).Select
I would like to enter it once, declare it I suppose, then call it?
Thanks for any help in getting me on the right foot with VBA.
I have a hopefully simple question.
Background: I have a set of data that is exported the same. The only difference is that there may be varying rows worth of data (A1:G5, A7:G11 vs A1:G3, A5:G7, A1:G10, A12:G21), but they will always fill cells A:G. I want to transform the data horizontally so I can import to Access.
Excel Workbook | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | A1 | B1 | C1 | D1 | E1 | F1 | G1 | ||
2 | A2 | B2 | C2 | D2 | E2 | F2 | G2 | ||
3 | A3 | B3 | C3 | D3 | E3 | F3 | G3 | ||
4 | A4 | B4 | C4 | D4 | E4 | F4 | G4 | ||
5 | A5 | B5 | C5 | D5 | E5 | F5 | G5 | ||
6 | |||||||||
7 | A7 | B7 | C7 | D7 | E7 | F7 | G7 | ||
8 | A8 | B8 | C8 | D8 | E8 | F8 | G8 | ||
9 | A9 | B9 | C9 | D9 | E9 | F9 | G9 | ||
10 | A10 | B10 | C10 | D10 | E10 | F10 | G10 | ||
11 | A11 | B11 | C11 | D11 | E11 | F11 | G11 | ||
Sheet2 |
Code:
Sub six_by_five()
'
' six_by_fiveMacro
' Macro recorded 11/6/2009 by Grant Harrington
'
'
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlDown)).Select
Range("D7:G11").Select
Range("G7").Activate
Selection.Copy
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Range("H1").Select
ActiveSheet.Paste
End Sub
Excel Workbook | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
1 | A1 | B1 | C1 | D1 | E1 | F1 | G1 | D7 | E7 | F7 | G7 | ||
2 | A2 | B2 | C2 | D2 | E2 | F2 | G2 | D8 | E8 | F8 | G8 | ||
3 | A3 | B3 | C3 | D3 | E3 | F3 | G3 | D9 | E9 | F9 | G9 | ||
4 | A4 | B4 | C4 | D4 | E4 | F4 | G4 | D10 | E10 | F10 | G10 | ||
5 | A5 | B5 | C5 | D5 | E5 | F5 | G5 | D11 | E11 | F11 | G11 | ||
6 | |||||||||||||
7 | A7 | B7 | C7 | D7 | E7 | F7 | G7 | ||||||
8 | A8 | B8 | C8 | D8 | E8 | F8 | G8 | ||||||
9 | A9 | B9 | C9 | D9 | E9 | F9 | G9 | ||||||
10 | A10 | B10 | C10 | D10 | E10 | F10 | G10 | ||||||
11 | A11 | B11 | C11 | D11 | E11 | F11 | G11 | ||||||
Sheet2 |
Using the above Macro on this data set
Excel Workbook | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | A1 | B1 | C1 | D1 | E1 | F1 | G1 | ||
2 | A2 | B2 | C2 | D2 | E2 | F2 | G2 | ||
3 | A3 | B3 | C3 | D3 | E3 | F3 | G3 | ||
4 | |||||||||
5 | A5 | B5 | C5 | D5 | E5 | F5 | G5 | ||
6 | A6 | B6 | C6 | D6 | E6 | F6 | G6 | ||
7 | A7 | B7 | C7 | D7 | E7 | F7 | G7 | ||
Sheet2 |
results in:
Excel Workbook | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
1 | A1 | B1 | C1 | D1 | E1 | F1 | G1 | D7 | E7 | F7 | G7 | ||
2 | A2 | B2 | C2 | D2 | E2 | F2 | G2 | ||||||
3 | A3 | B3 | C3 | D3 | E3 | F3 | G3 | ||||||
4 | |||||||||||||
5 | A5 | B5 | C5 | D5 | E5 | F5 | G5 | ||||||
6 | A6 | B6 | C6 | D6 | E6 | F6 | G6 | ||||||
7 | A7 | B7 | C7 | D7 | E7 | F7 | G7 | ||||||
Sheet2 |
Yet this code:
Code:
Sub Send_Key()
'
' six_by_three Macro
' Macro recorded 11/6/2009 by Grant Harrington
'
'
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlDown)).Select
SendKeys ("+{LEFT 3}")
SendKeys ("^{c}")
SendKeys ("^{g} H1")
SendKeys ("{ENTER}")
SendKeys ("^{v}"), True
End Sub
results in both of these, which is what I want
Excel Workbook | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
1 | A1 | B1 | C1 | D1 | E1 | F1 | G1 | D7 | E7 | F7 | G7 | ||
2 | A2 | B2 | C2 | D2 | E2 | F2 | G2 | D8 | E8 | F8 | G8 | ||
3 | A3 | B3 | C3 | D3 | E3 | F3 | G3 | D9 | E9 | F9 | G9 | ||
4 | A4 | B4 | C4 | D4 | E4 | F4 | G4 | D10 | E10 | F10 | G10 | ||
5 | A5 | B5 | C5 | D5 | E5 | F5 | G5 | D11 | E11 | F11 | G11 | ||
6 | |||||||||||||
7 | A7 | B7 | C7 | D7 | E7 | F7 | G7 | ||||||
8 | A8 | B8 | C8 | D8 | E8 | F8 | G8 | ||||||
9 | A9 | B9 | C9 | D9 | E9 | F9 | G9 | ||||||
10 | A10 | B10 | C10 | D10 | E10 | F10 | G10 | ||||||
11 | A11 | B11 | C11 | D11 | E11 | F11 | G11 | ||||||
Sheet2 |
and
Excel Workbook | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
1 | A1 | B1 | C1 | D1 | E1 | F1 | G1 | D5 | E5 | F5 | G5 | ||
2 | A2 | B2 | C2 | D2 | E2 | F2 | G2 | D6 | E6 | F6 | G6 | ||
3 | A3 | B3 | C3 | D3 | E3 | F3 | G3 | D7 | E7 | F7 | G7 | ||
4 | |||||||||||||
5 | A5 | B5 | C5 | D5 | E5 | F5 | G5 | ||||||
6 | A6 | B6 | C6 | D6 | E6 | F6 | G6 | ||||||
7 | A7 | B7 | C7 | D7 | E7 | F7 | G7 | ||||||
Sheet2 |
So, to my question.
I was advised to avoid using SendKey, and would like some basic help with the syntax for using the arrow keys and modifiers.
Selection.End(xlDown).Select = Ctrl+DownArrow
Range(Selection, Selection.End(xlDown)).Select = Ctrl+Shift+DownArrow
What is the syntax for Shift+LeftArrow
And is there a way to loop this code: Selection.End(xlDown).Select
So instead of
Selection.End(xlDown).Select
Selection.End(xlDown).Select
I would like to enter it once, declare it I suppose, then call it?
Thanks for any help in getting me on the right foot with VBA.
Last edited: