By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,780 Members | 1,126 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,780 IT Pros & Developers. It's quick & easy.

Delete Cells With Repeating Values

P: 1
Hello, I have a code like that, which has been using by me, but now I need another one, which won't differ a lot, I hope. Please, analize it, I believe that here I'll find somebody with large luggage of experience. Please, write any suggestions you have.

Expand|Select|Wrap|Line Numbers
  1. Sub test() 
  2.     Dim ws As Worksheet, a, i As Long, ii As Long, b(), n As Long, w(), e 
  3.     With CreateObject("Scripting.Dictionary") 
  4.         For Each ws In Worksheets 
  5.             With ws.UsedRange 
  6.     a = .Value 
  7.     .ClearContents 
  8. End With  
  9.             Redim b(1 To UBound(a, 1) * 2, 1 To UBound(a, 2)) 
  10.             For ii = 2 To UBound(a, 2)  Step 4 
  11.                 For i = 1 To UBound(a, 1) 
  12.                     If Not IsEmpty(a(i, ii)) Then 
  13.                         If Not .exists(a(i, ii)) Then 
  14.                             .Add a(i, ii),  Array(a(i, ii - 1), a(i, ii), a(i, ii - 1), a(i, ii)) 
  15.                         Else 
  16.                             w = .Item(a(i, ii)): w(2) = a(i, ii - 1): w(3) = a(i, ii) 
  17.                             .Item(a(i, ii)) = w 
  18.                         End If 
  19.                     End If 
  20.                 Next 
  21.                 For Each e In .items 
  22.                     n = n + 2 
  23.                     b(n - 1, ii - 1) = e(0): b(n - 1, ii) = e(1): b(n, ii - 1) = e(2): b(n, ii) = e(3) 
  24.                 Next 
  25.                 n = 0: .RemoveAll 
  26.             Next 
  27.             ws. Range("a1").Resize(UBound(b, 1), UBound(b, 2)).Value = b 
  28.             n = 0 
  29.         Next 
  30.     End With 
  31. End Sub 
I was using it to delete unnecessary coordinates from two columns, where first column is named 'x' (for arguments) and another one named 'y' for values. I mean I have a diagram, which is described by many coordinates. I need to delete these coordinates, which aren't useful for this diagram, because one straight line in diagram may be described by only two coordinates describing two points. Or I will say it more clearly - every line is described by linear function. Please check third row under title 'Before'. These columns are repeating after every next two empty columns. So after column A&B we should see E&F etc. Program above is used to delete these coordinates, where arguments have increasing values n+1, where n is any positive number expect zero. Values for each arguments repeat a few times, so it would be looking in Excel like that:

Before:
A B
1. 1 1
2. 2 2
3. 3 2
4. 4 2
5. 5 3
6. 6 3
7. 7 3
8. 8 3
9. 9 4
10. 10 4
11. 11 5
12. 12 6
13. 13 6
14. 14 6
15. 15 7
16. 16 7
.. .. ..

After:
A B
1. 1 1
2. 1 1
3. 2 2
4. 4 2
5. 5 3
6. 8 3
7. 9 4
8. 10 4
9. 11 5
10. 11 5
11. 12 6
12. 14 6
13. 15 7
14. 16 7
.. .. ..

But I would use the code for other purpose. I put now a force on two aspects - if there will be only one-column interval between columns with coordinates and another thing - if the scheme will look now inversely - instead X's will be Y's and vice versa, then I will see diagram in horizontal position. But I find a problem, because I don't know how to change the code. Please, help me anybody.
Dec 4 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.