Pulling values from a list which match specific dates - Excel VBA Loop

Question

I would really appreciate some help with excel VBA please -

I have 2 data columns on one sheet which also have blanks in between

  name         Date
   a        01-01-2019
   c        01-08-2019

   b        01-02-2019
   f        01-01-2019 

. . .

I am trying to display these columns organised in date order, without blanks, on another sheet in the same workbook. eg:

 name      date
  a       01-01-19
  f       01-01-19
  b       01-02-19
  c       01-08-19

I have tried using a For Each loop which contains some If statements, but it is not achieving the desired out come and I think will be very long-winded way of going about it - I'm new to VBA and I am struggling to get loops to work and have the correct result. I have looked at while loops but not sure if this is the correct way to go?

Thanks very much for your time!

Edit: Added in the code so far - (I'm aware it is not good)!

Dim r As Range
 Dim t As Range
 Dim r2 As Range
 Dim t2 As Range
 Dim rData As Range
 Set rData = Range("C4:C70") 

 Set r = Sheets("Sheet1").Range("D4") 
 Set r2 = Sheets("Sheet1").Range("C4")  
 Set t = Sheets("Sheet2").Range("D4") 
 Set t2 = Sheets("Sheet2").Range("C4")  


  For Each r2 In rData 
    If r.Value = "01/09/2018" Then
     t = r  
      t2 = r.Offset(0, -1)
    Set r = r.Offset(1, 0) 
      Set r2 = r2.Offset(1, 0) 
      Set t = t.Offset(1, 0) 
      Set t2 = t2.Offset(1, 0) 
    End If
   If r.Value <> "" & r2.Value <> "" Then
        Set r = r.Offset(1, 0) 
        Set r2 = r2.Offset(1, 0) 
       Set t = t.Offset(1, 0) 
        Set t2 = t2.Offset(1, 0) 

   End If
    If r.Value = "01/10/2018" Then
     t = r  
     t2 = r.Offset(0, -1)

    End If
  Next r2

Just copy and paste the whole then sort on the date. The blanks will go the bottom. — Scott Craner
– Scott Craner, Commented Aug 20, 2018 at 14:47
Can you edit your question with your For Each loop that you had? It's okay if it doesn't work. — dwirony
– dwirony, Commented Aug 20, 2018 at 14:47
@dwirony I have added the code but I'm not sure its a good starting point at all! — hsquared
– hsquared, Commented Aug 20, 2018 at 14:53
Why don't you just copy the whole worksheet and sort by date as Scott suggested? — Pᴇʜ
– Pᴇʜ, Commented Aug 20, 2018 at 14:55
@hsquared That's totally fine - people here like to see an attempt made, and you've clearly made one - it's not bad, but needs some work :) — dwirony
– dwirony, Commented Aug 20, 2018 at 14:55

dwirony · Accepted Answer · 2018-08-20 15:04:23Z

3

Here you go - a little cleaner than a recorded macro.

Sub SortDates()

Dim sht As Worksheet, sht2 As Worksheet, lastrow As Long

Set sht = ThisWorkbook.Worksheets("Sheet1")
Set sht2 = ThisWorkbook.Worksheets("Sheet2")
lastrow = sht.Cells(sht.Rows.Count, 3).End(xlUp).Row

sht2.Range("C1:D" & lastrow).Value = sht.Range("C1:D" & lastrow).Value

sht2.Sort.SortFields.Add Key:=Range("D1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
sht2.Sort.SetRange Range("C1:D" & lastrow)
sht2.Sort.Apply

End Sub

answered Aug 20, 2018 at 15:04

dwirony

5,4604 gold badges26 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

hsquared Over a year ago

This is absolutely perfect - thank you very much for taking the time to do this! I have not come across anything like this solution in the research I have done - Didn't know it could be done this way!

dwirony Over a year ago

No problem - there are several ways to go about this, but sorting was definitely your best option.

hsquared Over a year ago

An extension of this question (Which I have tried with no success) -Would it be possible to return only the rows which have values in the date column? In the data there are some which have no dates which I would like not included rather then placed at the bottom. I have tried things like If cell.Value = "" But this doesn't work and I feel its the wrong way to approach it.

dwirony Over a year ago

@hsquared yes, we can just clear contents to everything below your last line of dates - how far over do your columns go? Is it just column C and D?

hsquared Over a year ago

Ah yes that would make sense. I have modified your answer so it is now B to D columns which are used. But D, the Date column, is the last column with data in.

Collectives™ on Stack Overflow

Pulling values from a list which match specific dates - Excel VBA Loop

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related