VBA - Calculate interval between two dates

[OldAndWeak182]

Hi guys,
Would you be kind enough to help me out? I hit my head all day but finally gave up.
So...

first example:
In A1 I have 01/01/2021 and in A2 I have 05/01/2021 (european date).
In VBA how can I do to calculate the range "in between" and write for example in B1 01/01/2021, in B2 02/01/2021, in B3 03/01/2021 etc?

second example linked to the first:
Same scenario as before.
This time, however, I would like to insert variables: from B1 you must always write the range of dates but skip those that have been prescribed in another specific range X of cells, better still skip them based on the color of the cell of the X range (example 02/01/ 2021 is colored red so don't write it).

Good evening

 

[OldAndWeak182]

For the first example I came to this after several attempts

Sub date()
Dim j As Long, x As Long
    For j = 1 To Cells(2, 1) - Cells(1, 1) + 1
        Cells(j, 2) = Cells(1, 1) + x
        x = x + 1
    Next j
End Sub

someone help me for the second example?

 

[Marc L]

Hi,​

​

without any attachment it's just a guessing challenge …​

 

[OldAndWeak182]

Hi,​

​

without any attachment it's just a guessing challenge …​

Here is!

date sub.xlsm
	A	B	C	D	E	F	G	H	I	J
1	01/01/2021			second example	01/01/2021
2	05/01/2021			date to skip when execute 'cause red colour	02/01/2021
3					03/01/2021
4					04/01/2021
5					05/01/2021
6
7
8
9
Foglio1

 

[OldAndWeak182]

 

[Marc L]

According to your attachment a VBA beginner starter demonstration to paste to the worksheet module :​

Sub Demo1()
     Dim V, Rf As Range, R&, W
         If [A2<=A1] Then Beep: Exit Sub
         Application.ScreenUpdating = False
         [A1].Copy [B1]
    With [B1].Resize([A2-A1+1])
        .Cells(1).AutoFill .Cells
         V = .Value2
    With Application.FindFormat
        .Clear
        .Interior.Color = vbRed
    With UsedRange.Columns(5)
           Set Rf = .Find("", SearchFormat:=True)
        If Not Rf Is Nothing Then
                   R = Rf.Row
            Do
                   W = Application.Match(Rf.Value2, V, 0)
                   If IsNumeric(W) Then V(W, 1) = Empty
                   Set Rf = .Find("", Rf, SearchFormat:=True)
            Loop Until Rf.Row = R
                   Set Rf = Nothing
        End If
    End With
       .Clear
    End With
       .Value2 = V
    End With
        Application.ScreenUpdating = True
End Sub

 

[OldAndWeak182]

Dim V, Rf As Range, R&, W If [A2<=A1] Then Beep: Exit Sub Application.ScreenUpdating = False [A1].Copy [B1] With [B1].Resize([A2-A1+1]) .Cells(1).AutoFill .Cells V = .Value2 With Application.FindFormat .Clear .Interior.Color = vbRed With UsedRange.Columns(5) Set Rf = .Find("", SearchFormat:=True) If Not Rf Is Nothing Then R = Rf.Row Do W = Application.Match(Rf.Value2, V, 0) If IsNumeric(W) Then V(W, 1) = Empty Set Rf = .Find("", Rf, SearchFormat:=True) Loop Until Rf.Row = R Set Rf = Nothing End If End With .Clear End With .Value2 = V End With Application.ScreenUpdating = True End Sub

Works pretty well!
is there a way to eliminate the blank space so that the results are stacked one under the other anyway?

 

[Marc L]

​

Just add a Range.Sort statement …​

 

[Marc L]

​

Insert just before the last End With codeline this codeline : .Sort .Cells(1), 1, Header:=xlNo​

 

[OldAndWeak182]

​

Insert just before the last End With codeline this codeline : .Sort .Cells(1), 1, Header:=xlNo​

Sir, it is exactly what I was looking for. You are an ace, thanks again!