Solved

Remove all rows except ones containing selected date

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

ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

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

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
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.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

772 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