Hi All,
I have a dynamic table in a spreadsheet (but not a pivot-table) which basically looks in another sheet to see if a value is in that sheet and if so it populates the value in a second sheet. The idea is basically that we collect measurements in a specific (but variable) number of locations to show them on the sheet. The sheet then shows at the end the average and some other measurements.
One example is like the below:
If there was a smaller number of measurements there would be more of a gap between say Level 3 (W) and the Outside value, this could be 10 cells, it could be 2, or it could be more depending on how many levels have been tested.
What I am trying to do is to copy the table, but essentially remove the white space between the last measurement (in Level & Location), and the Outside / Average measurements which are on the table below. My issue is that this table is dynamic though, so I can't grab the whole table as there will be heaps of whitespace that I don't want to copy into a word report.
I know that the columns will be consistent, but not the rows. Does anyone have a code sample that might deal with something like this, where the data set stops, and there is an end set of rows, and they effectively want to copy the data like it is more like the below:
I am fine with copying data from between excel and word using VBA, just not too clear on how to best select the table leaving out whitespace, if it helps i know the maximum the table could ever be, and in the spreadsheet I do have the ability to work out how many locations and levels were tested so I could work out how many rows (i.e., 3 * 4) but is there a better way to do it to be able to copy a table and leave out the blank rows?
Many thanks!
I have a dynamic table in a spreadsheet (but not a pivot-table) which basically looks in another sheet to see if a value is in that sheet and if so it populates the value in a second sheet. The idea is basically that we collect measurements in a specific (but variable) number of locations to show them on the sheet. The sheet then shows at the end the average and some other measurements.
One example is like the below:
| Level | Location | Time | Measurement A | Measurement B | Measurement C |
1 | N | AM | 20 | 25 | 24 |
1 | S | AM | 21 | 25 | 19 |
1 | E | AM | 23 | 20 | 20 |
1 | W | AM | 23 | 25 | 21 |
2 | N | AM | 21 | 21 | 20 |
2 | S | AM | 23 | 24 | 25 |
2 | E | AM | 19 | 21 | 22 |
2 | W | AM | 24 | 24 | 24 |
3 | N | AM | 24 | 19 | 22 |
3 | S | AM | 24 | 24 | 23 |
3 | E | AM | 19 | 24 | 22 |
3 | W | AM | 25 | 22 | 25 |
| Outside | 20 | 22 | 24 | x | x |
| Average | 22 | 23 | 22 | x | x |
If there was a smaller number of measurements there would be more of a gap between say Level 3 (W) and the Outside value, this could be 10 cells, it could be 2, or it could be more depending on how many levels have been tested.
What I am trying to do is to copy the table, but essentially remove the white space between the last measurement (in Level & Location), and the Outside / Average measurements which are on the table below. My issue is that this table is dynamic though, so I can't grab the whole table as there will be heaps of whitespace that I don't want to copy into a word report.
I know that the columns will be consistent, but not the rows. Does anyone have a code sample that might deal with something like this, where the data set stops, and there is an end set of rows, and they effectively want to copy the data like it is more like the below:
| Level | Location | Time | Measurement A | Measurement B | Measurement C |
1 | N | AM | 20 | 25 | 24 |
1 | S | AM | 21 | 25 | 19 |
1 | E | AM | 23 | 20 | 20 |
1 | W | AM | 23 | 25 | 21 |
2 | N | AM | 21 | 21 | 20 |
2 | S | AM | 23 | 24 | 25 |
2 | E | AM | 19 | 21 | 22 |
2 | W | AM | 24 | 24 | 24 |
3 | N | AM | 24 | 19 | 22 |
3 | S | AM | 24 | 24 | 23 |
3 | E | AM | 19 | 24 | 22 |
3 | W | AM | 25 | 22 | 25 |
| Outside | 20 | 22 | 24 | x | x |
| Average | 22 | 23 | 22 | x | x |
I am fine with copying data from between excel and word using VBA, just not too clear on how to best select the table leaving out whitespace, if it helps i know the maximum the table could ever be, and in the spreadsheet I do have the ability to work out how many locations and levels were tested so I could work out how many rows (i.e., 3 * 4) but is there a better way to do it to be able to copy a table and leave out the blank rows?
Many thanks!
