Excel在工作表不为空白时执行 [英] Excel do while the sheet is not blank

问题描述

我的宏有问题:

' t Makro
'
' Keyboard Shortcut: Ctrl+t
'
    Selection.End(xlDown).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("List3").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Sheets("Hárok2").Select
    Application.CutCopyMode = False
    Selection.ClearContents

End Sub

我希望它继续进行，直到工作表完全空白为止，我应该在哪里插入代码以及它的外观如何?

And I would like it to continue till the sheet is totally empty, please, where should I insert code and how should it look like?

非常感谢您的帮助

推荐答案

简短的答案是，您需要将所有" selection "替换为范围变量，并将变量用于其他对象.宏记录器添加的"选择"和" .select "将导致其他问题.

The short answer is you need to replace all "selection"s with range variables, and also use variables for other objects. The "selection" and ".select" added by the macro recorder will cause problems otherwise.

例如，下面的代码将执行您的代码，假设每张纸上最初选择的单元格是 A1 ，并且顶部有空白行>Hárok2，但不在 List3 表的顶部.我之所以做出这些假设，是因为您没有提供数据的样子.

For example, below is some code that will do what your code does, assuming that the initially selected cell on each sheet is A1 and that the there are blank rows at the top of the Hárok2 but not at the top of the List3 sheet. I had do make these assumptions because you did not supply what your data looks like.

也许这段代码将提供一个起点.一旦将其应用于您的情况，就可以添加一个循环，这样它将清除工作表，如您所说.随时提出问题.

Perhaps this code will provide a starting point. Once you apply it to your situation, you can add a loop so that it will clear the sheet, as you said. Feel free to ask questions.

我在代码中添加了" .select "语句，以便您可以逐步了解它的工作原理，但是一旦您了解了它们的工作原理，就可以删除(并且应该删除)这些语句.

I added ".select" statements to the code so that you could step through it and see how it works, but those can be deleted (and should be) once you understand how things work.

Option Explicit
Dim sh1 As Worksheet, sh2 As Worksheet
Dim r1 As Range, r2 As Range
Sub transferTest()

Set sh2 = Worksheets("Hárok2")
Set r2 = sh2.Range("A1").End(xlDown)
r2.Select
Set r2 = sh2.Range(r2, r2.End(xlDown))
r2.Select
r2.copy
Set sh1 = Worksheets("List3")
sh1.Activate
Set r1 = sh1.Range("A1").End(xlDown).Offset(1, 0)
r1.Select
r1.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
sh2.Select
r2.ClearContents
End Sub

这篇关于Excel在工作表不为空白时执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！