Extract multiple date and time values to multiple rows

Hi, WilliamV,

if Data is always laid down as in your example and in this example there is enough empty cells under the given Data:

Sub WilliamV()
Dim varSplit
Dim lngCounter As Long
Dim lngOffset As Long

lngOffset = 0
varSplit = Split(ActiveCell.Value, " ")

For lngCounter = LBound(varSplit) To UBound(varSplit) Step 3
  With ActiveCell.Offset(lngOffset, 0)
    .Value = varSplit(lngCounter)
    .Offset(0, 1).Value = varSplit(lngCounter + 2)
    lngOffset = lngOffset + 1
  End With
Next lngCounter
End Sub

Ciao,
Holger

Hi, WilliamV,

if the range is filled and I have to insert I prefer to work from the bottom up. For this example I suggested that the last line of data in K should be the start for working the way up and splitting the contents.

Sub WilliamV_RangeAndInsert()

Dim varSplit
Dim lngCounter As Long
Dim lngOffset As Long
Dim blnNewColumn As Boolean
Dim lngRC As Long

Const clngSTEP As Long = 3
Const cstrCOL As String = "K"

For lngRC = Cells(Rows.Count, cstrCOL).End(xlUp).Row To 1 Step -1
  If blnNewColumn = False Then
    Cells(1, cstrCOL).Offset(0, 1).EntireColumn.Insert
    blnNewColumn = True
  End If
  With Cells(lngRC, cstrCOL)
    lngOffset = 0
    varSplit = Split(.Value, " ")
    
    .Offset(1, 0).Resize(WorksheetFunction.RoundDown(UBound(varSplit) / clngSTEP, 0), 1).EntireRow.Insert
    For lngCounter = LBound(varSplit) To UBound(varSplit) Step clngSTEP
      With .Offset(lngOffset, 0)
        .Value = varSplit(lngCounter)
        .Offset(0, 1).Value = varSplit(lngCounter + 2)
        lngOffset = lngOffset + 1
      End With
    Next lngCounter
  End With
Next lngRC

With Cells(1, cstrCOL).CurrentRegion
  .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
  .Value = .Value
End With

End Sub

Ciao,
Holger

Holger, ok, thank you.

I would like to apply this code to included file Uitrapport.xls. You will find a file Uitrapport_filter.xls as well. This last one imports (through VBA) data from the first one. When openening it will ask to select a source file, you need to select the file Uirapport.xls. I have used VBA and formula's to obtain needed results. In column N you can find the date- and timedata. At this moment, i only use a formula to extract the time data in format from HH:MM to HH:MM, and i extend this formula manually to the available range.
I would like to apply your above code to extract dates (if multiple present) and concerning times (per date, from and till). Multiople dates imply multiple rows, as in your examples above.

Can you help me out with this? Thank you.

• UitRapport.xls
• UitRapport_filter.xls

Hi, WilliamV,

according to the workbook in Column N the header is Lange beschrijving, and there are no date and time sequences as far as the Autofillter says. Can you help me out with that, please?

Ciao,
Holger

Hi, William,

okay, I got that one. But of I look at the first line another question occurs to me.

Contents in G2 is

van 05/09/12 tot 12/06/13   woe van 18:40 tot 21:35 (zo, ma, di, do, vrij, za gesloten)

No problem for the time but a small problem concerning about the dates. To me this would mean fill up a range of dates from 05/09/12 to 12/06/13 where the weekdays meet the weekdays indicated inside the parentheses (Sun, Mon, Tue, Thur, Fri - correct me if I´m wrong with figuring these out). So shall just be these or should all dates be added?

Ciao,
Holger

Hi, WilliamV,

back again

About the inserting of two columns for not overwríte data: can be done by something like this:

    If Not blnInserted Then
      With Cells(1, cstrCOL)
        .Offset(0, 1).Resize(1, 2).EntireColumn.Insert
        .Offset(0, 1).Resize(1, 2).ColumnWidth = 8
      End With
      blnInserted = True
    End If

I started putting the code to run in a loop from the bottom up - and got stuck on the very first line of data with an error. I doubt it is a very good idea to code every possible combination into the code.

As I don´t know how the data is coming to you it might be a better way to start and look at the contents of the cells and change them in a way to get a more consistent data with less exceptions to code for (that would make the code easier to maintain instead of writing new parts for any possible mistake that could appear). Like I said maybe there´s a way to straighten up the data - depending on how the data is delivered (easiest thing would be if you would supply a template to be filled where you could influence the data to yous/my needs).

Ciao,
Holger