VBA转到最后一个空行 [英] VBA Go to last empty row

问题描述

我在excel宏上有一个项目，我需要突出显示具有空值的下一行.示例单元格A1:A100有数据，下一个单元格A101为空.

I have a project on excel macro, I need to highlight the next last row that has an empty value. example cell A1:A100 have data and the next cell is A101 is empty.

用户单击按钮时，应突出显示单元格A101 ...

when user click a button it should highlight the cell A101...

推荐答案

如果确定只需要A列，则可以在VBA中使用End函数来获得该结果.

If you are certain that you only need column A, then you can use an End function in VBA to get that result.

如果所有单元格A1:A100都已填充，则选择下一个空单元格，请使用:

If all the cells A1:A100 are filled, then to select the next empty cell use:

Range("A1").End(xlDown).Offset(1, 0).Select

在这里，End(xlDown)等同于选择A1并按Ctrl +向下箭头.

Here, End(xlDown) is the equivalent of selecting A1 and pressing Ctrl + Down Arrow.

如果A1:A100中有空白单元格，那么您需要从底部开始并逐步向上.您可以通过结合使用Rows.Count和End(xlUp)来做到这一点，就像这样:

If there are blank cells in A1:A100, then you need to start at the bottom and work your way up. You can do this by combining the use of Rows.Count and End(xlUp), like so:

Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select

进一步讲，这可以概括为选择一系列单元格，从您选择的一点开始(不仅仅是在A列中).在下面的代码中，假设您在单元格C10:C100中具有值，并且在它们之间散有空白单元格.您希望选择所有单元格C10:C100，而无需知道该列在行100处结束，而是从手动选择C10开始.

Going on even further, this can be generalized to selecting a range of cells, starting at a point of your choice (not just in column A). In the following code, assume you have values in cells C10:C100, with blank cells interspersed in between. You wish to select all the cells C10:C100, not knowing that the column ends at row 100, starting by manually selecting C10.

Range(Selection, Cells(Rows.Count, Selection.Column).End(xlUp)).Select

作为VBA程序员，以上一行可能是更重要的一行，因为它使您可以基于很少的标准来动态选择范围，而不会被中间的空白单元格打扰.

The above line is perhaps one of the more important lines to know as a VBA programmer, as it allows you to dynamically select ranges based on very few criteria, and not be bothered with blank cells in the middle.

这篇关于VBA转到最后一个空行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！