Monday, 6 February 2017
excel - Loop to start at a cell and delete the next 600 in the column
Answer
Answer
All of my data is in the "D" column.
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
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?
Answer
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
No comments:
Post a Comment