Deleting Rows

[Sanjay2805]

I have a spreadsheet set up as follows

Start Date, W/CEnd Date, Space, Employer, End Date
25/01/2012, 25/01/2012, Space 1, Adam, 31/01/2012
01/02/2012, 08/02,2012, Space 1, Stacey, 14/02/2012
25/01/2012, 25/01/2012, Space 2, Steve, 31/01/2012
01/02/2012, 08/02,2012, Space 2, Adam, 14/02/2012

This Data goes on for 5 tabs and a lot of cells, what is the easiest way to build a macro that it will delete rows that do not fall into a specific week i.e. if I wanted the spreadsheet to only show space in a specific week i.e. week commencing 25/01/2012. I would want the spreadsheet only to show the following

Start Date, W/CEnd Date, Space, Employer, End Date
25/01/2012, 25/01/2012, Space 1, Adam, 31/01/2012
25/01/2012, 25/01/2012, Space 2, Steve, 31/01/2012

As I have to send this document out onto PDF and send it out I rather not use a filter and actually delete the cells as I am saving down a weekly file at the moment.

Any Help will be greatly appreciated.

[Don]

Hi and welcome!

So you don't want to use a filter because you think it deletes the cells? Or, are you saying that you want to delete the cells and that's why you want the macro to do it? Because, otherwise, a filter or a PivotTable are really the easiest things to use here.

[Sanjay2805]

Hi,

I want to delete the cells and then save it as a new worksheet with the week commencing date hence the need for the macro.

Hope this makes sense. It is my first time building a macro on a rather large scale so not sure what I can and cannot do.

Sanjay

[Don]

Try this:

Code: Select all

Sub Copy_Dates()

Dim start_date As Date
Dim end_date As Date
Dim curdate As Date


start_date = "1 / 25 / 2012"
end_date = "1 / 27 / 2012"


firstrow = 1
lastrow = Range("A" & Rows.Count).End(xlUp).Row

sheetname = "Sheet2"


For i = firstrow To lastrow

    curdate = Cells(i, 1).Value
   
    If curdate <= end_date And curdate >= start_date Then
        Range("A" & i).EntireRow.Copy
        newlastrow = Sheets(sheetname).Range("A" & Rows.Count).End(xlUp).Row + 1
        Sheets(sheetname).Range("A" & newlastrow).PasteSpecial
    End If

Next i

End Sub

This will copy the data to Sheet2. You can change that as needed, but it is assumed that this sheet already exists. The date range is entered near the top of the macro. Change the dates on these lines


start_date = "1 / 25 / 2012"
end_date = "1 / 27 / 2012"

as needed. Note that your date convention seems to be day first and month second and mine is month first and day second. But, as long as you are consistent between the date range here in the macro and also in the sheet, and, as long as the dates in the sheets are recognized as dates by Excel, everything should be OK.

hope this helps

[Sanjay2805]

Hi,

Thank you very much for this, I am a bit confused what the curdate is in your formula and the step where it goes

curdate = Cells(i, 1).Value

[Sisyphus]

Hi,
Don's code steps through your data, row by row. In each row it examines the date and decides whether to copy the row or not. The date being currently evaluated is the currdate.

[Sisyphus]

Hi,
Pardon me for being persistent, but you really don't need code to do this job. Had you used AutoFilter you would have been done long ago. Just to put the record straight I attach a file for demonstration. One Sheet1 you have a list of dates. An Autofilter is set. Click on the dropdown arrow at the top of column B an select "Custom" to see the parameters of the filter.
After applying the filter I selected the entire sheet (click in the blank corner square at the top of the row numbers and to the left of the column IDs), pressed Edit / Copy, selected A1 on Sheet2 and pressed Edit / Paste Special - Formulas.
By selecting Formulas I get the formulas in column D which I put there for the purpose of demonstrating that they can be copied. Had I selected plain Paste or Paste Special - Values I would have lost the formulas and retained the cell values - which I presume that you wish to avoid.
As you can see from the example, the rows hidden by the filter were not copied to Sheet2. Hence, Sheet2 is exactly what Don's macro would do once you get it to work.