在 Excel 2007 中选择要删除的行范围。运行时错误 1004？

发布于 2024-11-26 11:03:43 字数 384 浏览 0 评论 0原文

我正在尝试编写一个代码来删除工作表上第三个和最后一个数据行之间的所有行。我有一些简短的代码行，首先查找包含数据的最后一行，然后返回该行号。从中减去 1。并选择从第 3 行到第 2 行到最后一行的数据范围并尝试删除它们。但每次运行这段代码时我都会遇到错误。有什么建议吗？

Sheets("Sheet1").Activate
lastrow = (Sheet1.Range("A1").Offset(Sheet1.Rows.Count - 1, 0).End(xlUp).Row) - 1
Range("3: lastrow").Select  'Error 1004: method range of object _global failed
Selection.Delete Shift:=xlUp

原文

I'm trying to write a code that deletes all rows between the 3rd and last data row on a worksheet. I have some short lines of code that first looks for the last row containing data, returns that row number. Subtracts 1 from it. And selects the data range from 3rd row to the 2nd to last row and attempts to delete them. But I run into error every time I run this code. Any suggestions?

Sheets("Sheet1").Activate
lastrow = (Sheet1.Range("A1").Offset(Sheet1.Rows.Count - 1, 0).End(xlUp).Row) - 1
Range("3: lastrow").Select  'Error 1004: method range of object _global failed
Selection.Delete Shift:=xlUp

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

醉生梦死 2024-12-03 11:03:43

使用范围的 SpecialCells 属性：

 Range("A3:" & Range("A1").SpecialCells(xlCellTypeLastCell).Address).Select

这将选择直到最后使用的单元格为止的块。

编辑：
要将其合并到程序中以获取倒数第二个，请对最后一个单元格进行一些字符串操作。

Dim str, str1, str2, add As String
Dim index, num As Integer
str = Range("A1").SpecialCells(xlCellTypeLastCell).Address   'returns say $j$20

index = InStr(2, str, "$")  'find the second dollar sign

str1 = Left(str, index)     'gets the string "$j$"
str2 = Mid(str, index + 1)  'get the string "20" 

num = CInt(str2) 'convert "20" to 20
num = num - 1
add = str1 & CStr(num) 'reattach to form "$j$19"

Range("A3:" & add).Select

Using the SpecialCells property of the range:

 Range("A3:" & Range("A1").SpecialCells(xlCellTypeLastCell).Address).Select

This will select the block up until the last used cell.

Edit:
To incorporate it into your program to get the second to last, do some string manipulations on the last cell.

Dim str, str1, str2, add As String
Dim index, num As Integer
str = Range("A1").SpecialCells(xlCellTypeLastCell).Address   'returns say $j$20

index = InStr(2, str, "$")  'find the second dollar sign

str1 = Left(str, index)     'gets the string "$j$"
str2 = Mid(str, index + 1)  'get the string "20" 

num = CInt(str2) 'convert "20" to 20
num = num - 1
add = str1 & CStr(num) 'reattach to form "$j$19"

Range("A3:" & add).Select

回复收藏 0 原文

~没有更多了~