Excel VBA : single row of dates separated into new rows for each month -
i have column of dates (range jan through dec) have transposed 1 row.
i want separate row of dates (by month) new rows (one row per month).
if possible, i'd perform in same worksheet original row located. original column of data (c2:c54) has been transposed (d4:bd4). new rows separated month start on d5.
i have not found online keeps data in same worksheet , separates month in new row. hoping find here. appreciated , thank in advance.
this vba job you, if i've understood question correctly:
sub dt_trans() 'i numeric value offset each picked date 'by 1 column, , reset when new month picked mprev = month(range("d4")) 'loop through each cell in range each c in range("d4:bd4").cells 'if reach new month, set value 0 'so new row starts column d again if month(c) <> mprev = 0 end if 'offset d4 the month value move down rows 'i value move across columns range("d4").offset(month(c), i) = c.value 'iterate avoid overwriting = + 1 'set mprev month can compared next cell mprev = month(c) next end sub
Comments
Post a Comment