CODEWITHSUNDEEP
excelvba
Bhavesh Shaha
Bhavesh Shaha

Reputation: 783

Delete empty rows of the last sheet using VBA

Sub DeleteEmptyRows()
    For b = 1 To 10
        If Worksheets(Sheets.Count).Range(b, 1).Value = "" Then Worksheets(Sheets.Count).Rows(b).Delete
    Next b
End Sub

I am running a macro through Module1 that generates new sheets containing extrapolated data of Sheet1 using Sheets.Add After:=Sheets(Sheets.Count)

I am able to reference the rows of this new sheet using Worksheets(Sheets.Count).Rows(1)

However, I'm unable to make Sub DeleteEmptyRows() work.

Upvotes: 0

Views: 49

Answers (1)

FaneDuru
FaneDuru

Reputation: 42256

Please, try the next way:

Sub DeleteEmptyRows()
   Dim b As Long, ws As Worksheet, rngDel As Range
   
   Set ws = Worksheets(Sheets.Count)
    For b = 1 To 10
        If ws.Range(b, 1).Value = "" Then
            If rngDel Is Nothing Then
                Set rngDel = ws.Range(b, 1)
            Else
                Set rngDel = Union(rngDel, ws.Range(b, 1))
            End If
    Next b
    If Not rngDel Is Nothing Then rngDel.EntireRow.Delete
End Sub

Upvotes: 1

Related Questions