[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 391
  • Last Modified:

Excel VBA delete blank row between dynamic range

Hi expert

I would like to delete some blank rows in a table with dynamic range, some times the table will have more rows and some times it will be less.
The range to be deleted are restricted between the hightlighted yellow range (i.e. between B8 and B59(variable)) in the attahced example.

Thanks
ACD-Collection-Summary-1203--.xls
0
desmondwkng
Asked:
desmondwkng
1 Solution
 
dlmilleCommented:
App reads from line 10 to the next yellow line.  It looks at all cells from A:J and if empty, marks for deletion.  Once the next yellow line is found, all rows marked for deletion are deleted.

The code would be shorter just checking for null in column A, but I thought best to check every column where you have data, thus the inner loop.

There's a button for this on your sheet.

Option Explicit

Sub delblankRows()
Dim wkb As Workbook
Dim wks As Worksheet
Dim rng As Range
Dim r As Range
Dim chkRow As String
Dim rng2 As Range
Dim r2 As Range
Dim rDelete As Range
Dim lastRow As Long

    Set wkb = ThisWorkbook
    Set wks = wkb.ActiveSheet
    
    lastRow = wks.Cells.Find(what:="*", LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlPrevious).Row
    
    Set rng = wks.Range("A10:A" & lastRow)
    
    For Each r In rng
        If r.Interior.Color = vbYellow Then
            Exit For 'exit when hit next yellow line
        End If
        Set rng2 = wks.Range("A" & r.Row & ":J" & r.Row)
        For Each r2 In rng2
            chkRow = chkRow & Trim(r2.Value)
        Next r2
        If chkRow = vbNullString Then
            If rDelete Is Nothing Then
                Set rDelete = r
            Else
                Set rDelete = Union(r, rDelete)
            End If
        Else
            chkRow = vbNullString
        End If
    Next r
    
    If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
End Sub

Open in new window


See attached workbook.

Cheers,

Dave
ACD-Collection-Summary-1203-r1.xls
0
 
desmondwkngAuthor Commented:
thanks so much
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now