Solved

Excel VBA delete blank row between dynamic range

Posted on 2012-03-16
2
379 Views
Last Modified: 2012-03-17
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
Comment
Question by:desmondwkng
2 Comments
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 37733338
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
 

Author Closing Comment

by:desmondwkng
ID: 37733572
thanks so much
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wanted to share this with fellow Experts, who might not know. How often have you wanted to learn something, only to be set back by either restrictions imposed on "trial" or "evaluation" software?  How often have you had to rebuild a home networ…
The canonical version of this article is on my web site here: http://iconoun.com/articles/collisions/ A companion presentation is available here: http://iconoun.com/articles/collisions/Unicode_Presentation.pdf
Viewers will learn the different options available in the Backstage view in Excel 2013.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question