vba Excel 宏 - 选择所有数据和格式为表格的单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21557916/
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
Excel Macro - Select all cells with data and format as table
提问by Janu
Is it possible to write a macro that can format a table out of any active selection? For instance, I have a macro that will basically just do a Ctrl+Shift+End range selection. After that, I would like the macro to be able to format the selected range as a table, however when I record this action in VBA, it will use the range addresses, which will not always be the same from sheet to sheet.
是否可以编写一个可以从任何活动选择中格式化表格的宏？例如，我有一个宏，它基本上只会执行 Ctrl+Shift+End 范围选择。之后，我希望宏能够将所选范围格式化为表格，但是当我在 VBA 中记录此操作时，它将使用范围地址，这些地址在工作表之间并不总是相同。
Sub A_SelectAllMakeTable()
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A:$AO59"), , xlYes).Name _
= "Table1"
Range("A1:AO2959").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium15"
End Sub
Thanks in advance.
提前致谢。
回答by Dmitry Pavliv
Try this one for current selection:
试试这个用于当前选择：
Sub A_SelectAllMakeTable2()
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes)
tbl.TableStyle = "TableStyleMedium15"
End Sub
or equivalent of your macro (for Ctrl+Shift+End range selection):
或等效于您的宏（用于 Ctrl+Shift+End 范围选择）：
Sub A_SelectAllMakeTable()
Dim tbl As ListObject
Dim rng As Range
Set rng = Range(Range("A1"), Range("A1").SpecialCells(xlLastCell))
Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes)
tbl.TableStyle = "TableStyleMedium15"
End Sub
