Solved

Select all rows that have the same date

Posted on 2015-02-09
22
78 Views
Last Modified: 2016-02-11
Hello:
I have dates in column B that has been sorted so all date are in sequence.  Is there a way to select a cell and have all the rows that all have that date selected for me?  I would like to cut them out, (shifting up) and move them to another sheet tab and place these new rows inserting them at row 8 of a different page, pasting in only values and killing the formulas in the rows.  (shifting everything down)  Would like to do this for one day at a time, instead of archive all at once.

How do I get Excel to search for the block of dates equal to the date that I start with?  I want to put a rule in there that says, the date has to be at least a week old.  Please advise and thanks.
0
Comment
Question by:RWayneH
  • 12
  • 10
22 Comments
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
Have you tried using Auto Filter?

Even if the dates were not sorted, when filtering and then copying only the visible rows are copied. When pasted into another sheet they will be converted to values anyway and the data will be pasted as a block, ie not allowing for the hidden rows between. However, if you want the new data at the top you will have to insert rows to accommodate, bottom left of the Message bar will tell you how many rows were shown by the filter. Alternatively paste onto the bottom of existing data and then resort in reverse order by date.

The selection criteria for the filter can refer to a cell containing a date.

In order to then get rid of those rows from the source data, deleting rows using menu options or "Ctrl & -" will only delete visible rows after a warning message regarding deleting Entire Rows.

Thanks
Rob H
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
Another alternative would be to use Advanced Filter. This would effectively do the Copy and Paste for you.

However, the Paste part of the extraction will overwrite existing data but I don't see that as an issue in this case as you will always be extracting data that is more than one week old so the number of rows in the extract is always going to increase, thus adding to the Extracted data.

If your source data is sorted such that the newest is at the top, the extracted data should also be sorted in that manner so you will effectively be pasting new rows at the top each time.

Thanks
Rob H
0
 

Author Comment

by:RWayneH
Comment Utility
Ok an autofilter could work here...  yes...  here is what I have so far.  Hope it makes sense.

Sub ArchiveSelectedRows()
'
    'Need a vbaYesNo alert box, Asking "(Are you sure you want to archive filtered data???")  'For accidential button presses
    'Yes continue, No?  Exit Sub

    'if there is not an autofilter applied, warning and Exit sub
    'If there is a date in the selection that is < a week old, warning and exit sub.
    
    

'here need to select all visible filtered rows after row 7 (which is where the headers are. 
    
    
    Selection.Cut
    Sheets("LogArchived").Select
    Rows("8:8").Select
    Selection.Insert Shift:=xlDown
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Sheets("TaskTracker").Select
    ActiveSheet.UsedRange.AutoFilter Field:=2  'Remove filter?
    Range("B7").Select
    
    End Sub

Open in new window

0
 

Author Comment

by:RWayneH
Comment Utility
Wondering if I need to put something in that makes sure there are some visible rows?  Filter could be on and no records there...  Not sure, the user is going to run the autofilter prior to archive...  I would hope that there are some there, however the word dummyproof does come to mind.
0
 

Author Comment

by:RWayneH
Comment Utility
I am testing with:

  Dim rng As Range
  Set rng = ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible)
  rng.Select

However this is selecting everything above row 7 too.  Row 6 is my headers so I still need to tell it... if nothing is there to select, sending a msgbox warning, if there is something there? Select those visible rows.  How do I tell it to only select from row 7 and down?
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
Away from pc until tomorrow so can't work on it.
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
Try recording a VBA with Advanced Filter copying to a separate sheet. Recording of Advanced Filter will put all 3 parameters as ranges, you can change these to Range Names, eg Data, Criteria & Extract; Extract only needs to be 1 row with headers.

Advanced Filter requires the headers in the Criteria table and the Extract area to be the same as the Data headers. You don't have to use all headers and they don't have to be in the same order.

1) Filter and Copy to separate location (different to archive sheet),
2) Check number of entries is greater than 1, keep note of count,
3) Insert rows on archive sheet to accommodate count,
4) On extract sheet insert row at row 2, putting blank row between header and data. Select cell A3, top left of extract data after row inserted,
5) Use Ctrl + * to select Current Region. Copy and paste into Archive sheet and then delete,
6) Repeat Filter and Filter in place and Delete all,
7) Show all data and Insert row where headers should be. Copy headers from Archive sheet to Data sheet.

How does that logic sound?

Thanks
Rob H
0
 

Author Comment

by:RWayneH
Comment Utility
This is what I have so far...  it is close, but there are a couple of spots that I am struggling with.  My headers are in row 6.  I need a little help selecting rows instead of cells, and the syntax to tell it to look at rng for dates that are < 7 days old.

Sub ArchiveSelectedRows()
    
    'Assumes that the autofilter is already applied.
    
    Dim varAnswer As String
    varAnswer = MsgBox("Are you sure you want to archive data?  Click on No button to Exit.    ", vbYesNo, "To contine with archive, Press yes.")
        If varAnswer = vbNo Then
            Exit Sub
        End If
    
 'Issue, does not select row, selects cells... !!  so insert xlDown  Ln31  fails.
 'Need this to select the rows, not the cells.
 
  Dim rng As Range
  Set rng = ActiveSheet.AutoFilter.Range.Offset(1, 0)
  Set rng = rng.Resize(rng.Rows.Count - 1)
  Set rng = rng.SpecialCells(xlCellTypeVisible)
  rng.Select
  
  If b = Now() < 7 Then 'Need to check that user did not select anything that has a date < then a week old
    'b has to = column B of rng
    MsgBox ("The autofiltered range has a date in it that is < a week old.  Re-autofilter data and try again.")
    Exit Sub
  End If
  
  Selection.Cut
  Sheets("LogArchived").Select
  Rows("8:8").Select
    
    'Need to insert paste special values to take formulas out.
    Selection.Insert Shift:=xlDown  'need whole row selected here so it send everything.
    
    'my attempt to get formulas out.
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Sheets("TaskTracker").Select
    ActiveSheet.UsedRange.AutoFilter Field:=2
    Range("B7").Select
    
    End Sub

Open in new window

0
 

Author Comment

by:RWayneH
Comment Utility
Thinking though the setting of the autofilter and found this that applies the Autofilter  was not sure if the header row needs to be selected before it runs... the header row just happen to be active, and it applied the autofilter for me.

Selection.AutoFilter Field:=2, Criteria1:="Dates > 7days old."  

This way I can grab them all.  I wanted to start by doing one day at a time to get the process down, but it makes sense to start with a filter that gets all the items at once, leaving only a weeks worth of data in.

How would I tell the Criteria1:=  to be Dates > than 7 days old?    I want keep a weeks work of data.
0
 
LVL 31

Assisted Solution

by:Rob Henson
Rob Henson earned 500 total points
Comment Utility
Filtering on dates is always an issue. Would you be averse to a helper column alongside the date column?

Alongside the date column use formula:

=B2<=TODAY()-7

This will give TRUE or FALSE, then filter on that column for those that equal TRUE.

Selection.AutoFilter Field:=3, Criteria1:="TRUE"

Thanks
Rob H
0
 

Author Comment

by:RWayneH
Comment Utility
Thanks Rob!
I added the T/F formula that will be in a hidden column.  This will allow me to remove Ln20-24.  However, how would it react if the filter produces no rows of data, and all = False?  The hidden column with T/F should not matter.

On the issue with Ln14 thru Ln18 it selects cells.  How do I tell Excel to select the rows instead cells there?
This is very close now, just need to clean up a couple more things.  Please advise and thanks.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
When a filter is in place, if you select cells and delete it will delete whole rows anyway.

You could put a count check using the xlvisible property that you were using before or a SUBTOTAL function above the headers that counts the number of visible rows and have the routine check that value.
0
 

Author Comment

by:RWayneH
Comment Utility
The issues are that Selection.Cut will not work, unless the rows are selected, and Selection.Copy does, but then when trying to insert the rows in the destination sheet, it fails to put them in..  How do I get Ln14 thru 18, to select rows?  Even with a count check... the result still needs to have the rows from the autofilter selected, not the cells.  The process would be to Selection.Cut, so the row are removed from the target, and added to the destination when inserted.  Process is failing on the Selection.Cut   Unless you have a different idea on how to migrate items from a target sheet that has an autofilter applied to a destination sheet that gets them insert at the top and pushes the rest down?
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
Do a count of visible rows, go to destination sheet, insert required rows, go back to source and copy, paste into destination and then delete from source
0
 

Author Comment

by:RWayneH
Comment Utility
PLEASE!! I need rows selected!!  I have too many hidden columns for a standard paste to work..  copy selects the visible cells of the row.  I would like to use Selection.cut because it kills the rows from the source after.  Also inserting will automatically install whatever the selection is and the proper number of rows.  Plus I do not know how to do a count visible rows.....     Is there a way to select the ROWS that are produced from an autofilter?   If no, then I will need help with this visible row insert thingy, but looking forward, even that and select the desired cell in the destination sheet, the paste that does not include hidden columns and the data is not lining up.  If there is a command to get the visible ROWS produced from an autofilter, I believe we are done....  and it will be much cleaner and easier to work with.  If not, then I guess some sort of working around will be needed.
0
 
LVL 31

Assisted Solution

by:Rob Henson
Rob Henson earned 500 total points
Comment Utility
When copying filtered data, copying of only visible rows AND  columns is normal behaviour. If you want the data from the hidden columns you will have to unhide them before copying.

Simplest way to do the count would be to put a SUBTOTAL function in a spare cell:

=SUBTOTAL(3,A:A)

I think its 3 for count, worth checking help. SUBTOTAL only looks at visible rows.

Then pull the value of that cell into the routine for number of rows needed, after allowing for headers etc.
0
 

Author Comment

by:RWayneH
Comment Utility
Could you assist in the code edits to accomplish this?  Because not being able to use rows causes issues with the rest of the code.  What was the answer to my question?  Can the visible rows be selected instead of the cells?

Using this SUBTOTAL, creates the need to insert them in the destination, which I do not know how to do.  It also creates another process to go back to target sheet and delete the rows.. etc.

Yes or No, can the rows be selected instead of the cells in Ln14 thru 18?  You are introducing elements that I do not know how to do?   However being able to select those visible rows, and I am done. One edit instead of three.

Yes or No please?
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
I do not understand why you need to select whole rows, surely you want to copy the data with columns as far right as required, including hidden columns by the sound of it.

I believe you can paste and insert without selecting whole rows, manual equivalent which might be worth recording to get syntax is Ctrl & + after the copy has been done, ie data is on clipboard and flashing dashes (marching ants!) are still around copied data.

I believe this will give a count of the rows:

ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Rows

Sorry but I don't have Yes/No answer.

Did you try the Advanced Filter steps I suggested?

Can you upload a sample file and I will have a look but it won't be until tomorrow evening, tied up at work all day.
0
 

Author Comment

by:RWayneH
Comment Utility
The code to select the rows is:
Range(rng.Row & ":" & rng.Row + rng.Rows.Count - 1).Select

Open in new window


This finished the procedure, I will make final edits and post.  Thamks for the help.
0
 

Accepted Solution

by:
RWayneH earned 0 total points
Comment Utility
Here is a copy of the code that I landed on and a copy of the file.  It is a great tool to track activities throughout a day and how much time was spent on them.  Thanks for the help with this.  

Sub ArchiveSelectedRows()
 Sheets("TaskTracker").Select
 Range("B6").Select
' Selection.AutoFilter Field:=7, Criteria1:="TRUE"  'Adds a filter to keep a weeks worth of data in TimeTracker sheet
    
 Dim varAnswer As String
 varAnswer = MsgBox("Are you sure you want to archive data?  Click on No button to Exit.    ", vbYesNo, "To contine with archive, Press yes.")
    If varAnswer = vbNo Then
        Exit Sub
    End If
  
  If ActiveSheet.AutoFilterMode = False Then
    MsgBox ("Systems has deteched that there is not an autofilter applied.  It is not advised to clear the whole sheet.  This procedure is being termiated, however it is turning on the autofilter.  Please select date/s that you want to archive, and try again.")
    Rows("6:6").Select
    Selection.AutoFilter
    Exit Sub
  End If
  
  Dim rng As Range
  Set rng = ActiveSheet.AutoFilter.Range.Offset(1, 0)
  Set rng = rng.Resize(rng.Rows.Count - 1)
  Set rng = rng.SpecialCells(xlCellTypeVisible)
  rng.Select
  Range(rng.Row & ":" & rng.Row + rng.Rows.Count - 1).Select
  
  Selection.Cut
  Sheets("LogArchived").Select
  Rows("8:8").Select
  'Need to insert paste special values to take formulas out.
  Selection.Insert Shift:=xlDown  'need whole row selected here so it send everything.
  Cells.Select
  Selection.Copy
  Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
  Application.CutCopyMode = False
  Range("B8").Select
  
  Sheets("TaskTracker").Select
  Selection.Delete
  Selection.AutoFilter 'turnoff autofilter
  Range("B7").Select
End Sub

Open in new window

TimeTracker.xls
0
 

Author Closing Comment

by:RWayneH
Comment Utility
Thanks for the help with this.
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
Just found this on another thread:

Range("C1").AutoFilter Field:=1, Criteria1:="2015"
Range(Range("C2"), Range("C" & Cells.Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
ActiveSheet.Range("C1").AutoFilter

Open in new window

in your scenario, you would change line 2 so that your selected range starts at the correct row, ie the row below the headers, if headers in row 7 then change

Range(Range("C2")....
to
Range(Range("C8")....

Thanks
Rob H
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

771 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