PDA

View Full Version : Code to be used for all the ranges in the column



Tharabai
07-27-2015, 06:25 AM
I have used the below code to generate the dates within the date range (H3:I3)



Sub GenerateDates1()
Dim FirstDate As Date
Dim LastDate As Date
Dim NextDate As Date

FirstDate = Range("H3").Value
LastDate = Range("I3").Value

NextDate = FirstDate
Range("H4").Select

Do Until NextDate >= LastDate
NextDate = DateAdd("d", NextDate, 1)
ActiveCell.Value = NextDate
ActiveCell.Offset(1, 0).Select

Loop
End Sub




Can someone help me. I need to check all the date range in the column H and I.

Very Urgent… very grateful if someone can provide me the coding.

-Tharabai

p45cal
07-27-2015, 08:23 AM
try this macro while your sheet3 is the active sheet:
Sub blah()
For Each cll In Columns("I:I").SpecialCells(xlCellTypeConstants, 1).Cells
If IsDate(cll.Value) And IsDate(cll.Offset(, -1).Value) Then
'cll.Select
x = cll.Value - cll.Offset(, -1).Value
If x >= 1 Then
cll.Offset(1).Resize(x).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
cll.Offset(, -1).AutoFill Destination:=cll.Offset(, -1).Resize(x + 1), Type:=xlFillDefault
End If
End If
Next cll
End Sub

Tharabai
07-27-2015, 04:44 PM
Awesome !! works like a magic...

Thank you so much for your timely help :)

one more addition to the above, I want to fill the column A (ID) values also. The ID should be same as previous cell value.

-Tharabai

p45cal
07-28-2015, 02:01 AM
Sub blah2()
For Each cll In Columns("I:I").SpecialCells(xlCellTypeConstants, 1).Cells
If IsDate(cll.Value) And IsDate(cll.Offset(, -1).Value) Then
x = cll.Value - cll.Offset(, -1).Value
If x >= 1 Then
cll.Offset(1).Resize(x).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
cll.Offset(, -1).AutoFill Destination:=cll.Offset(, -1).Resize(x + 1), Type:=xlFillDefault
cll.Offset(, -8).Copy Destination:=cll.Offset(1, -8).Resize(x)
End If
End If
Next cll
End Sub

Tharabai
07-28-2015, 10:27 AM
Thank you so much.. It really helped me :)