Solved

Remove all rows except ones containing selected date

Posted on 2012-03-12
1
197 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

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

25 Experts available now in Live!

Get 1:1 Help Now