Solved

Remove all rows except ones containing selected date

Posted on 2012-03-12
1
200 Views
Last Modified: 2012-07-17
Good Morning Experts;

I have an Excel Workbook that performs a series of tasks before creating a CSV file for export into Timberline.

My boss has asked that the control worksheet now remove all rows from sheet Labor_Totals_Temp except for ones containing a user prompted date. I've attached a copy of the spreadsheet in its working state. Stage 4 is the button that should trigger the search and removal of unneeded entries in sheet Labor_Totals_Temp.

Simply put the following should occur.

User clicks on Stage 4 on sheet "Controls"
User is prompted for a date in format mm/dd/yyyy
Macro goes to sheet "Labor_Totals_Temp" and deletes all rows that do NOT have the prompted date.


Regards,
Brian
Z-Master-Daily-Converter-2007---.xlsm
0
Comment
Question by:RedstoneIT
1 Comment
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 37714007
Public Sub DeleteNonDateRows()
    Dim dtDate As Date
    Dim strDate As String
    Dim wks As Worksheet
    Dim rng As Range
    Dim rngCell As Range
    Dim rngDelete As Range
    
    strDate = InputBox("Enter date (mm/dd/yyyy)", "Date prompt", Date)
    If IsDate(strDate) Then
        dtDate = strDate
    Else
        MsgBox "Invalid date entered.  Please try again", vbCritical
        Exit Sub
    End If
    Set wks = Worksheets("Labor_Totals_Temp")
    Set rng = wks.Range("B2")
    Set rng = wks.Range(rng, rng.End(xlDown))
    For Each rngCell In rng
        If rngCell.Value <> dtDate Then
            If rngDelete Is Nothing Then
                Set rngDelete = rngCell.EntireRow
            Else
                Set rngDelete = Union(rngDelete, rngCell.EntireRow)
            End If
        End If
    Next
    If rngDelete Is Nothing Then
    Else
        Application.ScreenUpdating = False
        'Debug.Print rngDelete.Address
        rngDelete.Delete
        Application.ScreenUpdating = True
    End If
End Sub

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Outlook Free & Paid Tools
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

740 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