Macro for identifying distinct days from time stamp doesn't work

A

Andy0311

Board Regular
Joined
Oct 16, 2019
Messages
118
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello,
I run a seat time report for 30 teachers which measures the time they spent online (distinct days).

[TABLE="width: 688"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Teacher[/TD]
[TD]Course[/TD]
[TD]Date[/TD]
[TD]Time
(hh:mm)[/TD]
[TD]IP Address[/TD]
[/TR]
[TR]
[TD]Allison, Robert[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]*Home Page/eMail/Forum[/TD]
[TD]10/1/2019 5:39 AM[/TD]
[TD]0:00[/TD]
[TD]71.72.124.152[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]*Home Page/eMail/Forum[/TD]
[TD]10/1/2019 6:44 AM[/TD]
[TD]0:00[/TD]
[TD]71.72.124.152[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Math Integrated Math III (1-18)[/TD]
[TD]10/1/2019 6:44 AM[/TD]
[TD]0:03[/TD]
[TD]71.72.124.152[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]*Home Page/eMail/Forum[/TD]
[TD]10/1/2019 6:47 AM[/TD]
[TD]0:00[/TD]
[TD]71.72.124.152[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Math Integrated Math III (1-18)[/TD]
[TD]10/1/2019 6:47 AM[/TD]
[TD]0:06[/TD]
[TD]71.72.124.152[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]*Home Page/eMail/Forum[/TD]
[TD]10/1/2019 6:53 AM[/TD]
[TD]0:00[/TD]
[TD]71.72.124.152[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]*Home Page/eMail/Forum[/TD]
[TD]10/1/2019 9:08 AM[/TD]
[TD]0:00[/TD]
[TD]71.72.124.152[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Math Integrated Math III (1-18)[/TD]
[TD]10/1/2019 9:08 AM[/TD]
[TD]0:00[/TD]
[TD]71.72.124.152[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]*Home Page/eMail/Forum[/TD]
[TD]10/1/2019 9:08 AM[/TD]
[TD]0:01[/TD]
[TD]71.72.124.152[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Math Integrated Math III (1-18)[/TD]
[TD]10/1/2019 9:09 AM[/TD]
[TD]0:00[/TD]
[TD]71.72.124.152[/TD]
[/TR]
</tbody>[/TABLE]

The date column is a time stamp and I first have to take that data and separate the data the Text to Column function. Then I can take the date column and use Remove Duplicates, leaving me the unique days the teacher was online. My problem is that it works correctly for some teachers and incorrectly for others. Each teacher report is in a separate tab in my workbook. I need to know what I did wrong to make this macro work at times and at not. Any help would be appreciated. Here's the macro code:

Sub DistinctDays()
'
' DistinctDays Macro
'


'
Columns("D:F").Select
Range("D3").Activate
Selection.UnMerge
Columns("B:H").EntireColumn.AutoFit
Range("D6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("A4").Select
Columns("J:J").ColumnWidth = 27.71
Range("J6").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.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
ActiveSheet.Range("$J$6:$J$511").RemoveDuplicates Columns:=1, Header:=xlNo
Columns("E:F").Select
Selection.Delete Shift:=xlToLeft
Columns("I:J").Select
Selection.Delete Shift:=xlToLeft
Range("H4").Select
ActiveCell.FormulaR1C1 = "=COUNTA(R[2]C:R[18]C)"
Range("H5").Select
End Sub

Thank you in advance.

Andy
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
a few things to help people understand what you're trying to accomplish:
- use code tags when posting code
- show an end example of it working and it not working
- explain "my problem is it works correctly"; what is "it" and how do we know if it works correctly?

in the mean time i'll try and decipher what your code is doing, but you should state the steps that the code is intending to do as someone may know a more efficient way of doing what you want. Without running your code i have no idea what we're trying to accomplish here aside from removing duplicate dates
 
Upvote 0
this is what i have after running your code and i have a feeling its not whats intended because the beginning where it wants to select D:F and unmerge something doesn't happen when i copy/paste the data you provided.


Book1
ABCDEFGH
1TeacherCourseDateTime
2(hh:mm)
3Allison, Robert
4*Home Page/eMail/Forum10/1/2019 5:390:004
5*Home Page/eMail/Forum10/1/2019 6:440:00
6Math Integrated Math III (1-18)10/1/2019 6:440:0312:03
7*Home Page/eMail/Forum10/1/2019 6:470:0012:00
8Math Integrated Math III (1-18)10/1/2019 6:470:0612:06
9*Home Page/eMail/Forum10/1/2019 6:530:0012:01
10*Home Page/eMail/Forum10/1/2019 9:080:00
11Math Integrated Math III (1-18)10/1/2019 9:080:00
12*Home Page/eMail/Forum10/1/2019 9:080:01
13Math Integrated Math III (1-18)10/1/2019 9:090:00
Sheet1


if this is any indication, can you explain in as much detail as possible what you wish to do using column letters, row numbers, sheet names, etc
 
Upvote 0
@Andy0311, can you also let us know what column letters the columns shown in your sample in post number one apply to in reality.

Thank you.

Edit: @BlakeSkate, just to let you know that I got the same results as you when running the code after a straight copy/paste into A1.
 
Last edited:
Upvote 0
BlakeSkate said:
this is what i have after running your code and i have a feeling its not whats intended because the beginning where it wants to select D:F and unmerge something doesn't happen when i copy/paste the data you provided.

ABCDEFGH
TeacherCourseDateTime
(hh:mm)
Allison, Robert
*Home Page/eMail/Forum
*Home Page/eMail/Forum
Math Integrated Math III (1-18)
*Home Page/eMail/Forum
Math Integrated Math III (1-18)
*Home Page/eMail/Forum
*Home Page/eMail/Forum
Math Integrated Math III (1-18)
*Home Page/eMail/Forum
Math Integrated Math III (1-18)

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10/1/2019 5:39[/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10/1/2019 6:44[/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10/1/2019 6:44[/TD]
[TD="align: right"]0:03[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]12:03[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10/1/2019 6:47[/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]12:00[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10/1/2019 6:47[/TD]
[TD="align: right"]0:06[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]12:06[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10/1/2019 6:53[/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]12:01[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10/1/2019 9:08[/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10/1/2019 9:08[/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10/1/2019 9:08[/TD]
[TD="align: right"]0:01[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10/1/2019 9:09[/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>














Sheet1​



if this is any indication, can you explain in as much detail as possible what you wish to do using column letters, row numbers, sheet names, etc
Click to expand...

Thanks for your help. The above sheet actually starts in column B. A is hidden. Before recording my macro, I saw that columns D, E, and F were merged. That's where the date and time stamp is. I can't separate the date from the time on merged columns so in unmerging the date and time stamp was in Col D, and E and F were blank columns and I deleted them. I copied column D outside the data set and ran Text to Columns. Then I removed the duplicate dates. Here is what the intended result looks like, shown in red:


[TABLE="width: 1033"]
<tbody>[TR]
[TD]Teacher[/TD]
[TD]Course[/TD]
[TD]Date[/TD]
[TD]Time
(hh:mm)[/TD]
[TD]IP Address[/TD]
[TD][/TD]
[TD="align: center"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Allison, Robert[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]*Home Page/eMail/Forum[/TD]
[TD]10/1/2019 5:39 AM[/TD]
[TD]0:00[/TD]
[TD]71.72.124.152[/TD]
[TD][/TD]
[TD]10/1/2019 12:00 AM[/TD]
[TD="align: right"]5:39:46[/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]*Home Page/eMail/Forum[/TD]
[TD]10/1/2019 6:44 AM[/TD]
[TD]0:00[/TD]
[TD]71.72.124.152[/TD]
[TD][/TD]
[TD]10/2/2019 12:00 AM[/TD]
[TD="align: right"]6:44:14[/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Math Integrated Math III (1-18)[/TD]
[TD]10/2/2019 6:44 AM[/TD]
[TD]0:03[/TD]
[TD]71.72.124.152[/TD]
[TD][/TD]
[TD]10/3/2019 12:00 AM[/TD]
[TD="align: right"]6:44:19[/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]*Home Page/eMail/Forum[/TD]
[TD]10/2/2019 6:47 AM[/TD]
[TD]0:00[/TD]
[TD]71.72.124.152[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6:47:45[/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Math Integrated Math III (1-18)[/TD]
[TD]10/2/2019 6:47 AM[/TD]
[TD]0:06[/TD]
[TD]71.72.124.152[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6:47:50[/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]*Home Page/eMail/Forum[/TD]
[TD]10/3/2019 6:53 AM[/TD]
[TD]0:00[/TD]
[TD]71.72.124.152[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6:53:03[/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]*Home Page/eMail/Forum[/TD]
[TD]10/3/2019 9:08 AM[/TD]
[TD]0:00[/TD]
[TD]71.72.124.152[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9:08:17[/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Math Integrated Math III (1-18)[/TD]
[TD]10/3/2019 9:08 AM[/TD]
[TD]0:00[/TD]
[TD]71.72.124.152[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9:08:21[/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]*Home Page/eMail/Forum[/TD]
[TD]10/3/2019 9:08 AM[/TD]
[TD]0:01[/TD]
[TD]71.72.124.152[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9:08:53[/TD]
[TD]AM[/TD]
[/TR]
</tbody>[/TABLE]


The intended result is a column showing the number of distinct days online. This sample data shows only 15 rows, but can be over 1000.
 
Last edited:
Upvote 0
Andy0311 said:
Thanks for your help. The above sheet actually starts in column B. A is hidden. Before recording my macro, I saw that columns D, E, and F were merged. That's where the date and time stamp is. I can't separate the date from the time on merged columns so in unmerging the date and time stamp was in Col D, and E and F were blank columns and I deleted them. I copied column D outside the data set and ran Text to Columns. Then I removed the duplicate dates. Here is what the intended result looks like, shown in red:


[TABLE="width: 1033"]
<tbody>[TR]
[TD]Teacher[/TD]
[TD]Course[/TD]
[TD]Date[/TD]
[TD]Time
(hh:mm)[/TD]
[TD]IP Address[/TD]
[TD][/TD]
[TD="align: center"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Allison, Robert[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]*Home Page/eMail/Forum[/TD]
[TD]10/1/2019 5:39 AM[/TD]
[TD]0:00[/TD]
[TD]71.72.124.152[/TD]
[TD][/TD]
[TD]10/1/2019 12:00 AM[/TD]
[TD="align: right"]5:39:46[/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]*Home Page/eMail/Forum[/TD]
[TD]10/1/2019 6:44 AM[/TD]
[TD]0:00[/TD]
[TD]71.72.124.152[/TD]
[TD][/TD]
[TD]10/2/2019 12:00 AM[/TD]
[TD="align: right"]6:44:14[/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Math Integrated Math III (1-18)[/TD]
[TD]10/2/2019 6:44 AM[/TD]
[TD]0:03[/TD]
[TD]71.72.124.152[/TD]
[TD][/TD]
[TD]10/3/2019 12:00 AM[/TD]
[TD="align: right"]6:44:19[/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]*Home Page/eMail/Forum[/TD]
[TD]10/2/2019 6:47 AM[/TD]
[TD]0:00[/TD]
[TD]71.72.124.152[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6:47:45[/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Math Integrated Math III (1-18)[/TD]
[TD]10/2/2019 6:47 AM[/TD]
[TD]0:06[/TD]
[TD]71.72.124.152[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6:47:50[/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]*Home Page/eMail/Forum[/TD]
[TD]10/3/2019 6:53 AM[/TD]
[TD]0:00[/TD]
[TD]71.72.124.152[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6:53:03[/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]*Home Page/eMail/Forum[/TD]
[TD]10/3/2019 9:08 AM[/TD]
[TD]0:00[/TD]
[TD]71.72.124.152[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9:08:17[/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Math Integrated Math III (1-18)[/TD]
[TD]10/3/2019 9:08 AM[/TD]
[TD]0:00[/TD]
[TD]71.72.124.152[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9:08:21[/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]*Home Page/eMail/Forum[/TD]
[TD]10/3/2019 9:08 AM[/TD]
[TD]0:01[/TD]
[TD]71.72.124.152[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9:08:53[/TD]
[TD]AM[/TD]
[/TR]
</tbody>[/TABLE]


The intended result is a column showing the number of distinct days online. This sample data shows only 15 rows, but can be over 1000.
Click to expand...

In my sample data, the result(3 days) worked. My problem is that sometimes all days are not counted the the result isn't correct. I ran the macro the other day and the number of distinct days should have been 20 but only 15 were counted. Thank you for all your help, and advice for posting properly. I am still learning the protocols.
 
Upvote 0
MARK858 said:
@Andy0311, can you also let us know what column letters the columns shown in your sample in post number one apply to in reality.

Thank you.

Edit: @BlakeSkate, just to let you know that I got the same results as you when running the code after a straight copy/paste into A1.
Click to expand...

Here the columns in my sample data. Please take a look at what I sent BlakeSkate if you have time. Thanks again for your help.

[TABLE="width: 1033"]
<colgroup><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD][/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]Teacher[/TD]
[TD]Course[/TD]
[TD]Date[/TD]
[TD]Time
(hh:mm)[/TD]
[TD]IP Address[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Allison, Robert[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]*Home Page/eMail/Forum[/TD]
[TD]10/1/2019 5:39 AM[/TD]
[TD]0:00[/TD]
[TD]71.72.124.152[/TD]
[TD][/TD]
[TD]10/1/2019 12:00 AM[/TD]
[TD="align: right"]5:39:46[/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]*Home Page/eMail/Forum[/TD]
[TD]10/1/2019 6:44 AM[/TD]
[TD]0:00[/TD]
[TD]71.72.124.152[/TD]
[TD][/TD]
[TD]10/2/2019 12:00 AM[/TD]
[TD="align: right"]6:44:14[/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Math Integrated Math III (1-18)[/TD]
[TD]10/2/2019 6:44 AM[/TD]
[TD]0:03[/TD]
[TD]71.72.124.152[/TD]
[TD][/TD]
[TD]10/3/2019 12:00 AM[/TD]
[TD="align: right"]6:44:19[/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]*Home Page/eMail/Forum[/TD]
[TD]10/2/2019 6:47 AM[/TD]
[TD]0:00[/TD]
[TD]71.72.124.152[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6:47:45[/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Math Integrated Math III (1-18)[/TD]
[TD]10/2/2019 6:47 AM[/TD]
[TD]0:06[/TD]
[TD]71.72.124.152[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6:47:50[/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]*Home Page/eMail/Forum[/TD]
[TD]10/3/2019 6:53 AM[/TD]
[TD]0:00[/TD]
[TD]71.72.124.152[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6:53:03[/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]*Home Page/eMail/Forum[/TD]
[TD]10/3/2019 9:08 AM[/TD]
[TD]0:00[/TD]
[TD]71.72.124.152[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9:08:17[/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Math Integrated Math III (1-18)[/TD]
[TD]10/3/2019 9:08 AM[/TD]
[TD]0:00[/TD]
[TD]71.72.124.152[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9:08:21[/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]*Home Page/eMail/Forum[/TD]
[TD]10/3/2019 9:08 AM[/TD]
[TD]0:01[/TD]
[TD]71.72.124.152[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9:08:53[/TD]
[TD]AM[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Any chance you can upload your workbook to cloud storage like dropbox or google drive?
I can't seem to replicate the merged cells
If you can upload it please include a second sheet that DOESN'T work for you with your current code.
If you can't upload it what cells specifically are merged in your example? just the header? give me a range of cells that are merged.

my guess to why your macro does not work on your other sheets is that the ranges of cells are different
it would be no problem to convert your macro into a dynamic one, but i need to make sure that were looking at the same data first.
 
Upvote 0
I have the same concerns as BlakeSkate at the moment but just out of interest what happens if you run the code below on a copy of your actual data.

Code: 
Sub DistinctDays2()
    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
 
Upvote 0
BlakeSkate said:
Any chance you can upload your workbook to cloud storage like dropbox or google drive?
I can't seem to replicate the merged cells
If you can upload it please include a second sheet that DOESN'T work for you with your current code.
If you can't upload it what cells specifically are merged in your example? just the header? give me a range of cells that are merged.

my guess to why your macro does not work on your other sheets is that the ranges of cells are different
it would be no problem to convert your macro into a dynamic one, but i need to make sure that were looking at the same data first.
Click to expand...


I can save my workbook in Google Drive, or send the file to you as an email. What should I do to share it with you? Thank you. Andy
 
Upvote 0
You must log in or register to reply here.

Similar threads

Guzzlr
  • Question Question
Stripping Time Stamp away from Date
2
Replies
12
Views
630
Alex Blakenburg
A
S
  • Solved
Web Query in VBA Loop through tables on page to choose get tags from table instead of text
2
Replies
13
Views
569
Domenic
D
S
  • Question Question
Keep getting an "Error 1004: item could not be found in the OLAP cube" when applying the filter to pivot table
Replies
5
Views
1K
RoryA
RoryA
B
  • Question Question
VBA exports worksheet from XLSM to XLXS matching all source properties doesn't keep print area
Replies
0
Views
80
brentcole
B
S
  • Question Question
Calculating time, I think
Replies
1
Views
434
Fluff
Fluff

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top