CODEWITHSUNDEEP
vba
Harvint Raj
Harvint Raj

Reputation: 69

How do i delete 5 rows after the lastrow in excel?

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

Answers (1)

DDV
DDV

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

Related Questions