Solved

Excel VBA delete blank row between dynamic range

Posted on 2012-03-16
2
383 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

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
This collection of functions covers all the normal rounding methods of just about any numeric value.
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…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

717 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