Reputation: 69
Hi i need to delete 5 rows in the sheet. there are two sets of data, one at the top and one at the bottom. 5 rows in between them. not all the 5 rows are empty rows. so i need a code to delete the rows.
i have tried the method below because there is always an empty row after the first set of data
Const fiveRow = 5
Dim lastrow As Long, r As Range, ws As Worksheet, i As Integer
Set ws = ActiveSheet
With ws
For i = 1 To 5
' This clears 5 rows
.Cells(.Rows.Count, 1).End(xlUp).Offset(i, 0).Resize(.UsedRange.Rows.Count, 1).EntireRow.Delete
Next i
End With
Upvotes: 0
Views: 648
Reputation: 2395
You can use something like the below:
Option Explicit
Sub DeleteGap()
Dim lRow As Long
Dim ws As Worksheet
Dim i As Long
Set ws = Sheets("Sheet1")
'first get the last row of the first set of data
'then use this as referenc to delete the gap
lRow = ws.Range("A1").End(xlDown).Row
'if all 5 are blank - delete
' ws.Range("A" & (lRow + 1), "A" & (lRow + 5)).EntireRow.Delete
'if not all 5 are blank then loop through to find blank
For i = lRow To lRow + 5
If ws.Range("A" & i).Value = "" Then
ws.Range("A" & i).EntireRow.Delete
End If
Next i
'clear the object
Set ws = Nothing
End Sub
Adjust the "A"
range according to your requirements.
Upvotes: 2