vba 在工作表中的列和行中选择 UsedRange
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20717826/
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
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-12 01:03:31 来源:igfitidea点击:
Selecting UsedRange in Columns and Rows in Sheet
提问by Behseini
- I have two sheets in my excel file,
DTMGISandDTMEdit - the
DTMEditsheet is empty - and I am trying to xopy everything from
DTMGISand Paste only Values to SheetDTMEdit
- 我的 excel 文件中有两张工作表，
DTMGIS并且DTMEdit - 工作
DTMEdit表是空的 - 我正在尝试将所有内容从
DTMGIS并仅粘贴值到工作表DTMEdit
I do not know how to select the UsedRangeonly and paste this to DMTEdfit
我不知道如何选择UsedRange唯一的并将其粘贴到DMTEdfit
Dim ws As Worksheet
Dim LastRow As Long, LastCoulmn As Long, Header As Long
Header = 2
Set ws = ThisWorkbook.Sheets("DTMGIS")
LastRow = ws.UsedRange.Rows.Count
LastCoulmn = ws.UsedRange.Column.Count
With ws.UsedRange
.Select
.Copy
End With
采纳答案by Netloh
Try something like this:
尝试这样的事情：
Sub CopyPasteValues()
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = ThisWorkbook.Sheets("DTMGIS")
Set ws2 = ThisWorkbook.Sheets("DTMEdit")
ws1.Range(ws1.UsedRange.Address).Copy
ws2.Range("a1").PasteSpecial xlPasteValues
End Sub
回答by brettdj
A variation below which
下面的一个变体
- avoids the assumption that the
UsedRangestarts from A1 - runs the copy without moving the formats as well (if you do use
.CopyuseApplication.CutCopyMode = Falseat the end)
- 避免假设
UsedRange从 A1 开始 - 运行副本而不移动格式（如果你在最后使用
.CopyuseApplication.CutCopyMode = False）
code
代码
Sub FastCopy()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = ThisWorkbook.Sheets("DTMGIS")
Set ws2 = ThisWorkbook.Sheets("DTMEdit")
ws2.Range(ws1.UsedRange.Address).Value = ws1.UsedRange.Value
End Sub
