vba 选择列并将“常规”格式应用于所有单元格 + 在按钮上放置宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17176045/
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
Select column and apply "General"-format to all cells + put macro on a button
提问by user1507035
I am looking to do the following:
我希望执行以下操作：
- When pressing a button, the Macro should be activated.
- The Macro selects Column H in Sheet 2 (same workbook). Not the whole column, just until data goes. The last line with data can be determined if after that line the next 10 lines are empty.
- For this selection, the "General" format is applied to every cell.
- After this, the same runs through for Column G.
- The Macro ends.
- 按下按钮时，应激活宏。
- 宏选择工作表 2（同一工作簿）中的 H 列。不是整个列，直到数据消失。如果在该行之后接下来的 10 行为空，则可以确定带有数据的最后一行。
- 对于此选择，“常规”格式应用于每个单元格。
- 在此之后，G 列也是如此。
- 宏结束。
I think it should be easily possible, but I am especially struggling with the "determining last line with data" part, as if applied to the whole column the PC massively slows down.
我认为这应该很容易实现，但我特别为“用数据确定最后一行”部分而苦苦挣扎，就好像应用于整个列一样，PC 会大大减慢速度。
Then, I am unsure where I should put the code (Sheet, ThisWorkbook, Module) as best practice.
然后，我不确定应该将代码（工作表、本工作簿、模块）作为最佳实践放在哪里。
回答by Andy G
There are other ways to do this, but the easiest generally is to choose a LARGE row number (will there ever be more than 20,000 rows?) and navigate upwards.
还有其他方法可以做到这一点，但最简单的方法通常是选择一个大行号（是否会超过 20,000 行？）并向上导航。
Range("H2", Range("H20000").End(xlUp)).NumberFormat = "General"
Range("G2", Range("G20000").End(xlUp)).NumberFormat = "General"
But you can also just format the entire columns:
但您也可以只格式化整个列：
Range("G:H").NumberFormat = "General"
this doesn't (these days) impact the size of the file.
这不会（这些天）影响文件的大小。
You want to click a button of the sheet to run the macro, so you could use a Form Controls, Button and the code would then be in a standard module.
您想单击工作表的按钮来运行宏，因此您可以使用表单控件、按钮，然后代码将位于标准模块中。
Added(from my comment):
添加（来自我的评论）：
This would work:
这会起作用：
Application.Intersect(ActiveSheet.UsedRange, Range("G:H")).NumberFormat = "General"
but it requires some error-handling, just in case these ranges don't intersect.
但它需要一些错误处理，以防这些范围不相交。
Responding to 2 further questions in comments:
回答评论中的另外两个问题：
Worksheets("Whatever").Activate
If NumberFormatapplied to the column doesn't work then there must be something else going on that interferes - or perhaps the data was faultywhen imported(?). Try:
如果NumberFormat应用于列不起作用，那么一定有其他事情会干扰 - 或者导入时数据可能有问题（？）。尝试：
Application.CalculateFull
or just use the specific range:
或者只使用特定范围：
Range("..").Calculate
If this doesn't work then you may have to copy the data to a blank column and delete the old column. Or perhaps copy and paste (maybe values) into the same range.
如果这不起作用，那么您可能必须将数据复制到空白列并删除旧列。或者可能将（可能是值）复制并粘贴到相同的范围内。
回答by Jon Crowell
You can format all cells in columns G and H as general without selecting the range or the sheet. You should always avoid selecting anything in your VBA code.
您可以将 G 列和 H 列中的所有单元格设置为常规格式，而无需选择范围或工作表。您应该始终避免在 VBA 代码中选择任何内容。
The de facto standard way of finding the last row with data is to start at bottom of the sheet and go up from there.
查找包含数据的最后一行的事实上的标准方法是从工作表的底部开始，然后从那里开始。
The following finds the last cell with data on sheet2 in both columns G and H. We use the greatest of the two to set the range we will use to apply general formatting.
下面在 G 和 H 列中查找 sheet2 上包含数据的最后一个单元格。我们使用两者中最大的一个来设置我们将用于应用常规格式的范围。
Sub GeneralFormatForAllPopulatedCells()
Dim wb As Workbook
Dim ws As Worksheet
Dim g As Long, h As Long
Dim lastRow As Long
Dim rng As Range
Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet2")
g = ws.Range("G" & ws.Rows.Count).End(xlUp).Row
h = ws.Range("H" & ws.Rows.Count).End(xlUp).Row
If g > h Then
lastRow = g
Else
lastRow = h
End If
Set rng = ws.Range("G1:H" & lastRow)
rng.NumberFormat = "General"
End Sub
You should place your code in a module, and make sure that Option Explicitappears at the top of the module so that variable declaration is required. You can turn this on for all modules by opening the options dialog from within the VBA editor: Tools --> Optionsand then checking the box next to Require Variable Declaration.
你应该把你的代码放在一个模块中，并确保它Option Explicit出现在模块的顶部，以便需要变量声明。您可以通过从 VBA 编辑器中打开选项对话框来为所有模块启用此功能：工具 --> 选项，然后选中Require Variable Declaration旁边的框。
