Hello,
A while back I was trying to figure out how to count distinct days from a column in a seat time report I run. Mark88 provided this code (see below), which works flawlessly. I run it for each sheet in my workbook by clicking on sheet 1, run the code, click on sheet2, run the code, etc. for each sheet. Could this code be modified so that it could be run in sheet1, and then automatically loop to run in every other sheet in my workbook? Here's the code:
Thanks again, Mark88 for the help in my original post.
Andy
A while back I was trying to figure out how to count distinct days from a column in a seat time report I run. Mark88 provided this code (see below), which works flawlessly. I run it for each sheet in my workbook by clicking on sheet 1, run the code, click on sheet2, run the code, etc. for each sheet. Could this code be modified so that it could be run in sheet1, and then automatically loop to run in every other sheet in my workbook? Here's the code:
Code:
Sub DistinctDays2Mark88MrExcel()
Dim myrow As Long, arr, mycell
Application.ScreenUpdating = False
Columns("D:F").UnMerge
Columns("B:H").EntireColumn.AutoFit
Range("D6:D" & Range("D" & Rows.Count).End(xlUp).Row).Copy Range("J6")
Columns("J:J").ColumnWidth = 27.71
Range("J6:J" & Range("J" & Rows.Count).End(xlUp).Row).TextToColumns Destination:=Range("J6"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
myrow = Cells(Rows.Count, "J").End(xlUp).Row - 5
arr = Range("J6").Resize(myrow)
With CreateObject("scripting.dictionary")
For Each mycell In arr
mycell = Trim(mycell)
If Not .Exists(mycell) Then
.Add mycell, Empty
arr(.Count, 1) = mycell
End If
Next mycell
Range("J6").Resize(myrow).ClearContents
Range("J6").Resize(.Count) = arr
End With
Columns("E:F").Delete Shift:=xlToLeft
Columns("I:J").Delete Shift:=xlToLeft
Range("H4").FormulaR1C1 = "=COUNTA(R[2]C:R[18]C)"
Application.ScreenUpdating = True
End Sub
Thanks again, Mark88 for the help in my original post.
Andy
Last edited by a moderator: