Solved

Excel VBA delete blank row between dynamic range

Posted on 2012-03-16
2
378 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
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…

856 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