#31
|
|||
|
|||
Thanks again...Will put this to work tomorrow. My significant other says you are awesome. I agree. This will be final step in my spreadsheet effort. You have been tremendous resource. |
#32
|
|||
|
|||
TRIED but got "type mismatch"
message for the following.... If Join(Application.Transpose(Application.Transpose(. Cells(r, 1).EntireRow.Value)), "") = _ Join(Application.Transpose(Application.Transpose(. Cells(r - 1, 1).EntireRow.Value)), "") Then I think I will just use the delete duplicates feature in excel and forsake the dupesheet feature. Thanks again for all your help. |
#33
|
|||
|
|||
I've never seen what this would actually be working on.
Care to provide a small sanitized spreadsheet so I can learn where this tripped up ? |
#34
|
|||
|
|||
I have already blown away the original sheet that had the duplications. I suspect that somewhere I have screwed up and had mixed data types. In order to provide you a spreadsheet to enable us to see where the problem had occurred, I would have to duplicate the spreadsheet and the error(s) within. Having had excel remove duplicates and having eliminated 5 of the columns from the scope of my effort I can not accurately replicate the original sheet and the data within.
|
#35
|
|||
|
|||
I have taken the new spread sheet that has had the dupes removed and tried the macro that you provided. I got the same error message. So I will be able to provide you a small sample spreadsheet to be analyzed by you. Will be later today.
|
#36
|
|||
|
|||
A B C
FACILITY ZIP CODE STATE AAAAAAA 111111 ?? ROW 7 BBBBBB 22222 ?? ROW 8 CCCCCC 333333 KY ROW 9 EEEEE 555555 MI ROW 10 DDDDDD 444444 AK ROW 11 Sub Remove_Dupes() 'ActiveSheet.Unprotect ' Remove_Dupes Macro Dim lr As Long, r As Long, wr As Long Application.ScreenUpdating = False 'row to write to wr = Sheets("dupesheet").Cells(Rows.Count, 1).End(xlUp).Row + 1 'rows to check for dupes With Sheets("FACILITY RECORDS") 'last row lr = .Cells(Rows.Count, "A").End(xlUp).Row 'start at bottom row and work up For r = lr To 2 Step -1 If .Cells(r, 1) = .Cells(r - 1, 1) Then 'compare the two rows ' credit Tim Williams ~~~ with great explanation at ' http://stackoverflow.com/questions/1...ows-in-a-sheet If Join(Application.Transpose(Application.Transpose(. Cells(r, 1).EntireRow.Value)), "") = _ Join(Application.Transpose(Application.Transpose(. Cells(r - 1, 1).EntireRow.Value)), "") Then 'copy and delete if equal .Cells(r, 1).EntireRow.Copy Sheets("dupesheet").Cells(wr, 1) .Cells(r, 1).EntireRow.Delete wr = wr + 1 End If End If Next r End With Application.ScreenUpdating = True ActiveSheet.Protect ' End Sub I get the error even with this small sample. Wondering if my excel software is wacked out |
#37
|
|||
|
|||
the rows and columns did not show up as a spreadsheet format in previous post. Will try and edit it
CAN NOT GET IT TO LOOK LIKE i WANT |
#38
|
|||
|
|||
FUGMAN, delete those goofy posts, in the reply portion, click Go Advanced, scroll down to Manage Attachments, Browse to the desired file then Upload.
|
#39
|
|||
|
|||
Another goofy post.... The sample file I made works after it would not work. The code is good. I am not sure what I did but it is now solid. I am now going to call it a done deal and once again I find I can not replicate the problem. Thank you so very much for all your help.
|
#40
|
|||
|
|||
You're welcome FUGMAN, glad you got things working.
If you're satisfied that your question has been answered please mark this topic as solved I believe under "Thread tools" near the top of the page you can do this. |
#41
|
|||
|
|||
NoSparks
I can not find where to declared that this topic has been solved. My project consist of 4 phases....the first 2 phases centered around data gathering...the last 2 phases centered around reaching out to individuals that were identified in data gathering phase. As I have now just entered into phase 3, I find I have another "final" item that I could use help with. I will start new thread for that issue. If you could assist me in locating the problem solved indicator, I will be delighted to indicate that it has been solved. |
#42
|
|||
|
|||
I don't really know as I've never posted a question.
Did a search... led to this https://www.msofficeforums.com/forum...ad-solved.html If you can't mark the thread as solved, PM one of the moderators. |
#43
|
|||
|
|||
I must be dumber than a bag of rocks. I see no provision for identifying a moderator or PMing a moderator.
|
#44
|
|||
|
|||
If they don't want to make it easy then forget it.
|
#45
|
|||
|
|||
Moving on.
Thanks again for all you help. You saved me about 2 months of 50 hour weeks |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
pivot not showing number format with commas | olybobo | Excel | 1 | 05-05-2016 05:09 PM |
Counting Blanks in 1 Column and Non-Blanks in Another | dogwood705 | Excel | 4 | 02-07-2015 08:45 AM |
Commas within fields in .csv files | gar | Excel | 1 | 01-29-2015 09:24 PM |
How to use mid or left functions to seperate city,st,zip with no commas | sinaranje | Excel | 4 | 11-11-2014 10:52 PM |
No inverted commas in word 2013. | tonycrossley | Word | 3 | 01-27-2014 04:55 PM |