Solved

Remove all rows except ones containing selected date

Posted on 2012-03-12
1
196 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
Comment Utility
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

743 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

16 Experts available now in Live!

Get 1:1 Help Now