excel - Can I make subtotal macro more efficient? -

i have sheet data pasted in format below. need count number of times there entry in each month, can see example below can occur on many days within month.

the way can think of separate month out , subtotal , copy totals.

trouble is taking ages run due amount on data.

is there way of going i'm not seeing? i've pasted current code below. tia

' add totals  sheets("data").select columns("g:g").select selection.numberformat = "mm" range("f4").select selection.subtotal groupby:=7, function:=xlcount, totallist:=array(7), _     replace:=true, pagebreaks:=false, summarybelowdata:=true activesheet.outline.showlevels rowlevels:=2 range("g3:g4000").select     selection.specialcells(xlcelltypevisible).select selection.copy sheets("set data").select range("b2").select activesheet.paste application.cutcopymode = false sheets("data").select range("d56").select application.cutcopymode = false selection.removesubtotal 

it faster copy data variant array, , analysis on that.

something this

sub demo()     dim rdata range     dim vdata variant     dim long     dim counts(1 12, 1 1) long      ' range reference source data     '   assumes data in column g, starting @ row 4.  adjust required     worksheets("data")         set rdata = .range(.cells(4, 7), .cells(.rows.count, 7).end(xlup))     end      ' copy range data variant array     vdata = rdata.value      ' count occurance of each month     = 1 ubound(vdata, 1)         ' allow possibility dates strings         counts(month(cdate(vdata(i, 1))), 1) = counts(month(cdate(vdata(i, 1))), 1) + 1      next      ' put count data on sheet     '   adjust target required     worksheets("set data").cells(2, 2).resize(ubound(counts, 1), 1) = counts  end sub