循环从一个单元格开始，然后删除该列中的下一个600 [英] Loop to start at a cell and delete the next 600 in the column

问题描述

我所有的数据都在"D"列中.

All of my data is in the "D" column.

我要启动一个特定的单元格(例如D249)，然后选择下一个600并删除它们(例如D250-D850).然后，我想跳过下一个(即D851)并删除下面的下一个600.我想这样做直到它结束，就像D19000一样.我想我需要使用while循环来检查我正在查看的单元格中是否存在数据.

I want to start a specific cell (ie. D249) and then select the next 600 and delete them (ie. D250-D850). Then I want to skip the next one down (ie. D851) and delete the next 600 below that. I want to do this until it gets to the end, which is like D19000. I think I need to use a while loop to check if data is present in the cell I'm looking at.

这是我到目前为止的代码:

This is the code I have so far:

Sub delete600rows()
'delete600rows Macro
'Keyboard Shortcut: Ctrl+a

ActiveSheet.Range("D249").Select
While LEN()>0

Range("D249:D848").Select
Selection.Delete Shift:=xlUp

End Sub

如何编写while循环的条件以及如何使范围选择按我希望的方式工作?

How do I write the condition for the while loop and how do I make the range select work the way I want it to?

推荐答案

以下方法应该可以使用.我没有对其进行广泛的测试.我放置了一个选项，您可以在其中选择要从其开始的单元格以及要删除的行数.如果这些是标准的，只需在代码中对其进行硬编码即可:)

The one below should work. I have not tested it extensively. I put an option where you can select the cell you want to start from and how many rows you want to delete. If those are standard just hard code them in the code :)

Sub deleteNumOfrows()
Dim myBook As Workbook
Dim mySheet As Worksheet
Dim startCell As Range
Dim numOfRowsToDelete As Long
Dim lastRow As Long
Dim i As Long

'Set your workbook - in this version I select the active workbook
Set myBook = ActiveWorkbook

'Set up your worksheet - In this version I select the active worksheet
Set mySheet = myBook.ActiveSheet

'Select the cell you want to start
Set startCell = Application.InputBox(prompt:="Select a cell", Type:=8)

'Input how many rows you want to delete - If it is always 600 just set it to that
numOfRowsToDelete = Application.InputBox(prompt:="Number of rows to delete each time")

'Find the last row data exists in the column of the startCell
lastRow = mySheet.Cells(mySheet.Rows.Count, startCell.Column).End(xlUp).Row

'Delete the rows you do not want
i = startCell.Row 'Start from the row of the startCell

While i <= lastRow 'Repeat while you haven't reached the last row
    'mySheet.Rows((i + 1) & ":" & (i + numOfRowsToDelete)).EntireRow.Delete 'Delete the rows
    mySheet.Range(Cells((i + 1), startCell.Column), Cells((i + numOfRowsToDelete), startCell.Column)).Delete Shift:=xlUp  'Delete the range you dont want
    i = i + 1 'Next row
    lastRow = lastRow - numOfRowsToDelete 'Adjust lastRow
Wend

End Sub

这篇关于循环从一个单元格开始，然后删除该列中的下一个600的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！