vba 如果 Excel 2007 中的前一行为空，则将数据向上移动一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11840479/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Move data up a row if preceding row is empty in Excel 2007
提问by user1581031
I have been trying to develop a macro that will allow me to cut and paste a value or text from one row to the upper row, if the upper row is empty in a some specific columns. (If possible, I'd like to delete the empty rows after that.)
我一直在尝试开发一个宏，如果某些特定列中的上一行为空，则允许我将值或文本从一行剪切并粘贴到上一行。（如果可能，我想在此之后删除空行。）
4137274 20120604 41.6 10.83 Tolerance exceeded
4137279 20120604 Tax code missing
55.9 -0.04
4137271 20120604
111.8 -11.26 Tolerance exceeded
4137325 20120604 Tax code missing
130.72 0
4139318 20120605 65.36 0 Tax code missing
4139114 20120605
223.6 -22.54 Tolerance exceeded
4139279 20120605
111.8 111.8 Tolerance exceeded
4139151 20120605 65.36 65.36 GR does not exist
4140802 20120605 27.95 -0.02 Tax code missing
4140732 20120605 27.95 27.95 GR does not exist
As you can see above, some of the prices are in the next row. I have to work with a large amount of data where it is not possible to cut and paste all of them manually. Is there a way to do this, with a macro or otherwise?
正如您在上面看到的，一些价格在下一行。我必须处理大量数据，而无法手动剪切和粘贴所有数据。有没有办法用宏或其他方式做到这一点？
回答by James L.
Without writing the macro for you, here is some pseudo-code and some pointers:
无需为您编写宏，这里有一些伪代码和一些指针：
- Move the selection to cell A1
- Test for a empty cell
- if empty, execute
Selection.Delete Shift:=xlUpand increment anempty counter - if not empty, zero
empty counterand move down
- if empty, execute
- When
empty counterreaches 10 (or some number to signal that no more data exists in that column), break out of the loop. - Move the selection to B1 and repeat
- Repeat for column C, etc.
- 将选择移动到单元格 A1
- 测试空单元格
- 如果为空，则执行
Selection.Delete Shift:=xlUp并增加一个empty counter - 如果不为空，则归零
empty counter并向下移动
- 如果为空，则执行
- 当
empty counter达到 10（或某个数字表示该列中不存在更多数据）时，跳出循环。 - 将选择移动到 B1 并重复
- 重复 C 列等。
Instead of doing a cut/paste to move values, doing the Selection.Delete Shift:=xlUpwill discard the empty cell and move everything under it upwards by one cell. It will not affect the other columns if you only have onecell selected.
不是通过剪切/粘贴来移动值，而是Selection.Delete Shift:=xlUp将丢弃空单元格并将其下方的所有内容向上移动一个单元格。如果您只选择了一个单元格，它不会影响其他列。
