Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 20
Default Delete Rows with particular dates

Is it possible to delete rows where dates that appear in column C,
have been defined within Sheet2 (basically delete bank holiday days
that I'll separately define in Sheet2).

Also, after these dates have then been deleted to go through and
delete any rows where the date is either day 6 or 7 (e.g. Saturday and
Sundays).

Thanks In Advance, Cheers - Al.
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,123
Default Delete Rows with particular dates

Hi Al

With the dates in sheet2 in column A
Select the cells in column C in Sheet1 or ? before you run the sub

Try this example

Public Sub FindNDelete()
Dim myRange As Range
Dim myCell As Range
Dim findText As String
Dim i As Long
Dim found As Boolean
Application.ScreenUpdating = False
found = False
For i = 1 To Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
findText = Worksheets("Sheet2").Cells(i, 1)
Application.StatusBar = "Finding " & findText
Set myRange = Selection
Do
Set myCell = myRange.Find(What:=findText, _
LookIn:=xlFormulas, lookAt:=xlWhole)
If Not myCell Is Nothing Then
myCell = ""
found = True
End If
Loop Until myCell Is Nothing
Next i
If found Then
myRange.SpecialCells(xlCellTypeBlanks).EntireRow.D elete
End If
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Al Mackay" wrote in message om...
Is it possible to delete rows where dates that appear in column C,
have been defined within Sheet2 (basically delete bank holiday days
that I'll separately define in Sheet2).

Also, after these dates have then been deleted to go through and
delete any rows where the date is either day 6 or 7 (e.g. Saturday and
Sundays).

Thanks In Advance, Cheers - Al.



  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,092
Default Delete Rows with particular dates

You can use Excel function Networkdays. You create a list of Holiday dates
for the function to use and it will also exclude days 6 & 7. Put this
function in an empty column adjoining your dates to test and use that one
date as the Start & End dates for the function ( a 1 day date range). The
result will be a 1 if the date is a work day or a 0 if it is day 6 or 7 or
on your Holiday list. Copy Fill-Down the function to the end of your data
and you will now have a 0 or 1 for each date in your data. Filter your
entire data by the 0's and delete them. All that is left are workdays.

Cheers...Mike F
"Al Mackay" wrote in message
om...
Is it possible to delete rows where dates that appear in column C,
have been defined within Sheet2 (basically delete bank holiday days
that I'll separately define in Sheet2).

Also, after these dates have then been deleted to go through and
delete any rows where the date is either day 6 or 7 (e.g. Saturday and
Sundays).

Thanks In Advance, Cheers - Al.



  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4
Default Delete Rows with particular dates

Hi Ron,

Apols for the delay in responding to this posting. For some reason this
didn't delete any data?

I've read on other postings that people have problems with date formats?
Could this be a problem that I may have?

Would it be easier if I had on my second sheet all dates that I want it to
delete (incl' weekends in here and just have the VBA deleting on this
condition?).

Thanks for your help on this.

Cheers, Al.

"Ron de Bruin" wrote:

Hi Al

With the dates in sheet2 in column A
Select the cells in column C in Sheet1 or ? before you run the sub

Try this example

Public Sub FindNDelete()
Dim myRange As Range
Dim myCell As Range
Dim findText As String
Dim i As Long
Dim found As Boolean
Application.ScreenUpdating = False
found = False
For i = 1 To Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
findText = Worksheets("Sheet2").Cells(i, 1)
Application.StatusBar = "Finding " & findText
Set myRange = Selection
Do
Set myCell = myRange.Find(What:=findText, _
LookIn:=xlFormulas, lookAt:=xlWhole)
If Not myCell Is Nothing Then
myCell = ""
found = True
End If
Loop Until myCell Is Nothing
Next i
If found Then
myRange.SpecialCells(xlCellTypeBlanks).EntireRow.D elete
End If
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Al Mackay" wrote in message om...
Is it possible to delete rows where dates that appear in column C,
have been defined within Sheet2 (basically delete bank holiday days
that I'll separately define in Sheet2).

Also, after these dates have then been deleted to go through and
delete any rows where the date is either day 6 or 7 (e.g. Saturday and
Sundays).

Thanks In Advance, Cheers - Al.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Hpw do I delete multiple empty rows found between filled rows? Bill Excel Worksheet Functions 2 November 15th 09 08:12 PM
How to Delete empty rows in excel in b/w rows with values Dennis Excel Worksheet Functions 3 August 28th 07 04:15 PM
Cut filtered rows, paste into next empty row of new sheet, and delete cut rows Scott Excel Worksheet Functions 0 December 13th 06 02:25 AM
How to delete rows when List toolbar's "delete" isnt highlighted? Linda Excel Worksheet Functions 1 May 26th 05 08:39 PM
Delete every 3rd row, then delete rows 2-7, move info f/every 2nd row up one to the end and delete the row below Annette[_4_] Excel Programming 2 September 21st 04 02:40 PM


All times are GMT +1. The time now is 03:27 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"