Select generic range in VBA

[harringg]

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 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

Excel 2003<br>

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

Excel 2003

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

Excel 2003

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

Excel 2003

Yet this 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

Excel 2003

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

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.

 

[RoryA]

If your data is a solid table surrounded by blank rows and columns, then you can refer to the Currentregion property of any cell within that table:

Range("A1").CurrentRegion.Select

for example. (though note that it is almost never necessary to actually select anything in code to manipulate it).

 

[Norie]

Grant

You really need to forget about using keystrokes.

There are plenty of ways to get ranges when the amount of data is changing.

One of the simplest is to get the last row with something like this.

LastRow = Range("A" & Rows.Count).End(xlUp).Row
 
Set rng = Range("A1:G" & LastRow)

There are other methods and techniques, such as what rory has suggested.

 

[harringg]

My head is spinning thinking about this. Using the absolute key strokes works.

Using the (modified) code: Range("G7").CurrentRegion.Select

Grabs A7:G11, I only want D7:G11

and when the next set of data starts at G93, I don't want to have to change the code.

But here's where I'm having questions.

I start with data in A1:G11 (A6:G6 are empty), I want to move D7:G11 to H1:K5. My final data set would include A1:K5 (the rest in A7:G11 is ignored)

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

Excel 2003


Or as in this example,
I start with data in A1:G7 (A4:G4 are empty), I want to move D5:G7 to H1:K3. My final data set would include A1:K3 (the rest in A5:G7 is ignored)

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

Excel 2003

If someone could post an example script to get these consistent results using proper VBA would be appreciated, one where I'm not using keystrokes to achieve them.

Something that will work even if I have code in A1:G41, A21:G21 are blank, and I want to move D22:G41 to H1:K20.

Since the Sub Send_Key() example in post #1 does work to achieve the results I want, any help for this VBA newbie would be appreciated.

 

[Norie]

Grant

It's probably me but I'm having a real problem with your examples.

I just can't seem to work out the logic behind what you want to do.

By the way I'm not referring to how to select/create the ranges, I mean the general idea behind things.

 

[harringg]

Grant

It's probably me but I'm having a real problem with your examples.

I just can't seem to work out the logic behind what you want to do.

By the way I'm not referring to how to select/create the ranges, I mean the general idea behind things.

Analytical chemistry data. Quantification reports are generated using proprietary software. The data can be exported as a .txt file and the layout is always the same.

That data is in blocks that contain the sample number, sample name, sample id (A1:C5) and the resulting data (D1:F5). That's for compound one with five samples in it.

The second row of data is compound two: sample number, sample name, sample id (A7:C11) and the resulting data (D7:F11).

I don't need the sample number, sample name, or sample id more than once in Access, but I do need the data from compound one and compound two. Access requires the data come in as horizontal data, and when my data is vertical, I need to transform it to horizontal.

So I'm trying to "move" the data from compound two (D7:F11) into the same row as compound one (H1:K5)

Dataset1 may have 50 samples (A1:F50, A52:F100) and Dataset2 may have 20 samples (A1:F20, A22:F41). I need an easy way, using the power of Excel to move that data around and make sure that there is no data lost. Manual copy/paste is tedious and time consuming and can (and has) lead to data getting lost during the paste process.

Simply put, I'd like to move a (fixed) chunk of data to fixed location regardless of how many samples (rows of data) there are.

If you would like a sample data file, PM me, and I'll send you one showing the raw data, and what I'm hoping to get it to look like using VBA and Excel.

I thought my request was fairly detailed, but if there is more information you need, please ask.

Thanks

p.s. Scientists use Excel too.

 

[harringg]

I appreciate the attempts to offer help, but when weighing the "correct" way to do things, versus the way that works and more importantly, a way I understand, in an effort to best utilize time spent getting my spreadsheet built and being expandable, I'll use the SendKey for now.

I would appreciate hearing the reasons why it's best to not mimic keystrokes as means of me having a better understanding of the overall power of VBA. Is it because of situations when handing off the spreadsheet to others, the possibility of keystrokes "breaking" the Macro, ect...? Since I'm the only one working on this spreadsheet and I'll create a button tied the Macro, I can't find any reason to not use the SendKey, in this instance.

Thanks again to those who have offered assistance.

 

[Norie]

Grant

I can't really comment on what to use instead of keystrokes, especially when I don't quite understand what you are trying to do.

I just know that SendKeys/keystrokes is definitely not the way to go.

You've posted plenty of examples, in this thread and others, but like I said I can't quite understand them.

That's maybe me though I think the last Chemistry I did was more than 20 years ago.

I seem to remember that you posted some sample data that actually had chemistry stuff in it rather than just cell references.

I think posting data like that would be more helpful than what you've posted in this thread.

 

[harringg]

You've posted plenty of examples, in this thread and others, but like I said I can't quite understand them.

I want to move a block of data from Range D7:G11 to Range H1:K5, export to Access, clear the spreadsheet.

Next I want to paste new data, move a block of data from Range D4:G7 to Range H1:K3, export to Access, clear the spreadsheet.

Next I want to paste new data, move a block of data from Range D4:G5 to Range H1:K3, export to Access.

All using the same Macro.

The raw data can be numbers of widgets, annual reports, chemistry data, warehouse inventory, it doesn't matter, it's just data. I'm just looking to move data from one location on the worksheet to another location.

The starting location of data will always start in the range Dx:Gx, but x will vary each time. The final location always starts in H1. I've found using keyboard shortcuts converted to VBA language works consistently and easily. It's the logic behind not using them I don't grasp simply because my knowledge of VBA is limited. It's really not an argument. Others who are more knowledgeable than me in VBA scripting have said this isn't a good way to do things, and I respect that opinion and really would like help achieving the same end result with my data using "proper" VBA code.

I don't know how much clearer I can be. I've provided the code I'm using, shown example data visually laid out the way my data starts out and how I want it to look when I'm done transforming it in Excel. If there is something I'm missing, please do tell.

Thanks and have a good weekend. Will check back on Monday.