vba 如何在 Excel 中更改活动单元格的位置（或将值提高一）

How can I change the position of the active cell (or raise the value with one) in Excel

提问by Peter O

After performing some formatting and date manipulation actions in a macro, I want to remove the extra rows below my list.

在宏中执行一些格式化和日期操作操作后，我想删除列表下方的额外行。

I've come with the following solution, but that deletes the last line but I need to delete everything below the last line...

我提供了以下解决方案，但这会删除最后一行，但我需要删除最后一行以下的所有内容...

Code:

代码：

'Delete garbage at the bottom:
1)    Range("A1").Select
2)    Selection.End(xlDown).Select
3)    Range(Selection, Selection.End(xlDown)).Select
4)    Range(Selection, Selection.End(xlDown)).Select
5)    Range(Selection, Selection.End(xlToRight)).Select
6)    Range(Selection, Selection.End(xlToRight)).Select
7)    Selection.Delete Shift:=xlUp

I created this by recording a macro. What is need is to lower the activecell one row after step 2. With step 2 I place my activecell ON the last line.

我通过录制宏创建了这个。需要的是在第 2 步之后将活动单元格降低一行。在第 2 步中，我将我的活动单元格放在最后一行。

I thought about the following solution (but I don't know how to do this)

我想到了以下解决方案（但我不知道该怎么做）

1) After step 2. Read out the current position of the Active cell. (Selection.Address) and put in in a variable and raise it with one. 2) Put cursor on the new position (Range("variable").Select 3) Do the rest of the magic.

1) 在第 2 步之后，读出 Active cell 的当前位置。(Selection.Address) 并放入一个变量中并用一个来提升它。2) 将光标放在新位置 (Range("variable").Select 3) 做剩下的魔术。

Anyone knows a good solution?

有谁知道一个好的解决方案？

UPDATE:

更新：

To answer my question:

回答我的问题：

ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate

This will change to position of the active cell with one row.

这将更改为一行的活动单元格的位置。

But I understand that using this method is unwanted, thanks for the answers.

但我知道使用这种方法是不需要的，感谢您的回答。

My complete Macro looks like this:

我的完整宏如下所示：

Sub Function1()
'
' Record Macro

'Split one column in multiple cells
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(7, 1), Array(20, 1), Array(30, 4), Array(36, 1), _
        Array(45, 4), Array(51, 1), Array(62, 1), Array(63, 1), Array(64, 1), Array(67, 1)), _
        TrailingMinusNumbers:=True
'Remove empty / uneeded Columns
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
    Columns("D:D").Select
    Selection.Delete Shift:=xlToLeft
'Formatting Cells
    Columns("E:E").Select
    Selection.NumberFormat = "#,##0.00"
'Insert Formula #1 on row 1
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "=RC[-5]/100"
'Autofill formula to row 5000 (There will never be more than 5000 lines)
    Selection.AutoFill Destination:=Range("J1:J5000")
    Range("K1").Select
'Insert Formula #1 on row 1
    ActiveCell.FormulaR1C1 = "=IF(RC[-5]=""+"",RC[-1],RC[-1]*-1)"
    Range("K1").Select
'Autofill formula to row 5000 (There will never be more than 5000 lines)
    Selection.AutoFill Destination:=Range("K1:K5000")
'Colomn K, copy/paste as Value
    Columns("K:K").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("K:K").Select
'Remove Columns that are not needed anymore
    Columns("E:G").Select
    Selection.Delete Shift:=xlToLeft
    Columns("F:G").Select
    Selection.Delete Shift:=xlToLeft
'Column 2 Search for "/" and remove it
    Columns("B:B").Select
    Selection.Replace What:="/", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
'Remove the unneeded Autofill lines
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Delete Shift:=xlUp
'Reset view
    Cells.Select
    Cells.EntireColumn.AutoFit

End Sub

I know this is unwanted, but by using the Macro recording function I was able to automate some tasks and save a lot of time!! I am very curious how this code can be optimized. If someone have some ideas, please let me know!

我知道这是不需要的，但是通过使用宏录制功能，我能够自动执行一些任务并节省大量时间！！我很好奇如何优化这段代码。如果有人有一些想法，请告诉我！

回答by Andreas N.

I'm bit of a novice to vba and Stackoverflow myself, but I suppose you could try to mix selection.offset with the worksheet.UsedRange and ClearContents like so:

我自己是 vba 和 Stackoverflow 的新手，但我想您可以尝试将 selection.offset 与 worksheet.UsedRange 和 ClearContents 混合使用，如下所示：

'select A1
ActiveSheet.Range("A1").select

'use Selection.End(xlDown).Offset(row,column) to provide the first range argument
'and "A" & worksheet.usedrange.rows.count for the second range argument
ActiveSheet.Range(Selection.End(xlDown).Offset(1, 0), _ 
"A" & ActiveSheet.UsedRange.Rows.Count).EntireRow.ClearContents

I am sure someone can supply you with a more generic solution, but I hope some of this could be of use for this particular issue of yours

我相信有人可以为您提供更通用的解决方案，但我希望其中一些可以用于您的这个特定问题

回答by Tony Dallimore

I assume you have a blank line between the rows you want to keep and the rows you want to delete. If so try:

我假设您要保留的行和要删除的行之间有一个空行。如果是这样，请尝试：

  Dim RowLastKeep As Long
  Dim RowLastDelete As Long

  ' It is rarely a good idea to assume the active worksheet is the one
  ' required.  Replace "Sheet1" with the name of your worksheet
  With Worksheets("Sheet1")

    RowLastKeep = .Cells(1, "A").End(xlDown).Row
    RowLastDelete = .Cells(Rows.Count, "A").End(xlUp).Row

    .Rows(RowLastKeep + 1 & ":" & RowLastDelete).EntireRow.Delete

  End With

Explanation

解释

There are a number of questions with answers that explain why it is a bad idea to use Select. For me, the biggest reason is that I find code like yours confusing.

有许多带有答案的问题可以解释为什么使用Select. 对我来说，最大的原因是我发现像你这样的代码令人困惑。

They are special cases when working with the Active Worksheet is appropriate but they are rare. I have seen code fail because the user was looking at the wrong worksheet when the macro was started. I always use the Withstatement so my code is explicit about which worksheet is its target. If you do this you must replace Range("A1")which references the active worksheet with .Range("A1")which references the worksheet defined by the Withstatement. Remembering to place all the dots in front of every Range, Cells, etc. takes practice but the end result is much clearer code.

当使用活动工作表时，它们是合适的特殊情况，但它们很少见。我看到代码失败，因为用户在启动宏时查看了错误的工作表。我总是使用该With语句，因此我的代码明确说明哪个工作表是其目标。如果您这样做，您必须将Range("A1")引用活动工作表的引用替换为引用语句.Range("A1")定义的工作表的引用With。记住将所有的点放在 every Range、Cells、 等前面需要练习，但最终结果是更清晰的代码。

I hope you agree that using Endbut getting the row number, rather than selecting a new active cell, gives clearer code.

我希望您同意使用End但获取行号，而不是选择一个新的活动单元格，可以提供更清晰的代码。

It may not matter now if the code is clear because you remember what you are attempting to achieve. But when you want to update this macro in six months, clear code is a life saver.

代码是否清晰现在可能无关紧要，因为您记得您试图实现的目标。但是当你想在六个月内更新这个宏时，清晰的代码就是救命稻草。