Solved

Excel VBA delete blank row between dynamic range

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

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
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…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

861 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now