[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Removal of Rows based on blank or "0" zero data entries

Posted on 2011-10-14
1
Medium Priority
?
171 Views
Last Modified: 2012-06-21
Good Evening Experts,

I have attached a sample workbook with the macro codes I am currently
using.

What I am needing now is a subroutine that will do the following:

On Sheet Labor Totals If ( Column B is blank or Zero) AND
(Column D is blank or Zero) then I want the row to be deleted.

On Sheet EQ Totals If ( Column C is blank or Zero) then
I want the row to be deleted.

 I'd like this to be a separate subroutine from the compilation
function I already have. This needs to function in both Excel
2003 and 2007.

Best Regards,
Brian


Sample-Workbook.xlsm
0
Comment
Question by:RedstoneIT
1 Comment
 
LVL 24

Accepted Solution

by:
StephenJR earned 2000 total points
ID: 36971175
Try this:
Sub x()

Dim rData As Range

Application.ScreenUpdating = False

With Sheets("Labor Totals")
    .Range("A1").Value = "a"
    .AutoFilterMode = False
    .Range("A1").AutoFilter Field:=2, Criteria1:="0", Operator:=xlOr, Criteria2:=""
    .Range("A1").AutoFilter Field:=4, Criteria1:="0", Operator:=xlOr, Criteria2:=""
    With .AutoFilter.Range
        On Error Resume Next
        Set rData = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not rData Is Nothing Then
            rData.EntireRow.Delete
        End If
    End With
    .AutoFilterMode = False
    .Range("A1").Clear
End With

Set rData = Nothing

With Sheets("EQ Totals")
    .Range("A1").Value = "a"
    .AutoFilterMode = False
    .Range("A1").AutoFilter Field:=3, Criteria1:="0", Operator:=xlOr, Criteria2:=""
    With .AutoFilter.Range
        On Error Resume Next
        Set rData = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not rData Is Nothing Then
            rData.EntireRow.Delete
        End If
    End With
    .AutoFilterMode = False
    .Range("A1").Clear
End With

Application.ScreenUpdating = True

End Sub

Open in new window

0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

873 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