Remove duplicated rows and sort by cell color

**martincast** · 09-26-2022, 11:56 PM

Hi all, I need to create two buttons for an excel sheet using VBA. Let me give you some context.

Let's say I have a sheet named "database". The complete "A" column is emtpy because I need it for somethign else, nothing so relevant, the information of the database is filled from column B to column K, and of course all records are listed below that, means that the row from B1 to K1 has the titles of the columns, information starts from B2-K2, B3-K3 and so on until the final record.

These record will keep increasing, new records will added, means the table "height" will variate a lot, but the "width" will be the same from B to K, and also, the column "K" will be empty of information BUT it will be filled with a color, I mean, a cell fill color and it will variate, yellow, brown, blue, red, pink, etc. ONLY COLUMN K WILL BE EMPTY OF INFO BUT FOR CELL FILL COLOR!

Now what I need, please help me.

Button 1: to eliminate rows with duplicated index in column B, it will cycle from start of the table to the end of the table/records removing the duplicated records withing the B column
Button 2: It will sort all the table using as it's reference the colors in the column K, I don't mind the order of the colors, it can be by the order how they are shown in the database, what I need is to all the blue records be together, also pink, red and all.

Please I'll appreciate your help.

**bebo021999** · 09-27-2022, 12:56 AM

Pls attach a sample worksheet.

**martincast** · 09-27-2022, 08:29 AM

I cant post link idk why, how can i post files?

**torachan** · 09-27-2022, 08:51 AM

see big yellow banner - explains how to

**martincast** · 09-27-2022, 10:32 AM

I think I uploaded it correctly

**jeffreybrown** · 09-27-2022, 10:37 AM

Please take a minute to update your thread title based on our Forum Rules.

**martincast** · 09-27-2022, 10:44 AM

I think I updated it correctly

**torachan** · 09-27-2022, 10:47 AM

opening the VBA in your file there appear to be two lots of code relevant to a Mac, what system are you using, please make this obvious in your profile.

**martincast** · 09-27-2022, 10:53 AM

Sorry for all the mess, it was windows 10 but i deleted the file, not sure what code was it but i must check again if there is no confidential information, sorry for the mess

**martincast** · 09-27-2022, 01:43 PM

here is now the sample without any confidental info, thanks and sorry for bothering

**Marc L** · 09-27-2022, 06:50 PM

According to your attachment for your Button 1 an Excel basics VBA demonstration
like any Excel beginner operating manually and using the Macro Recorder :

PHP Code:


Sub Macro1()

    [A1].CurrentRegion.RemoveDuplicates 2, 1

End Sub

► Do you like it ? ► ► So thanks to click on bottom left star icon ? ★ Add Reputation ? ! ◄ ◄

**Marc L** · 09-27-2022, 08:29 PM

According to the attachment a VBA demonstration for the Button 2 :

PHP Code:


Sub Macro2()

        Dim R&, C&, oCol As New Collection

    With ActiveSheet.AutoFilter.Sort

       .SortFields.Clear

        On Error Resume Next

    For R = 2 To .Rng.Rows.Count

        C = .Rng(R, 1).Interior.Color

        Err.Clear

        oCol.Add C, Str(C)

        If Not Err.Number Then .SortFields.Add(.Rng(1), 1, 1, , 0).SortOnValue.Color = C

    Next

        On Error GoTo 0

        Set oCol = Nothing

       .Header = 1

       .Apply

       .SortFields.Clear

    End With

End Sub

► Do you like it ? ► ► So thanks to click on bottom left star icon ? ★ Add Reputation ? ! ◄ ◄

**martincast** · 09-29-2022, 09:53 AM

Thank you very mcuh all let me try it!

**martincast** · 09-29-2022, 12:01 PM

Thank you it work just needed some modifications but works!

**Marc L** · 09-29-2022, 02:56 PM

As I wrote : " according to the attachment " ...

Thanks for the rep' !

Remove duplicated rows and sort by cell color

LinkBack

Thread Tools

Rate This Thread

Display

Remove duplicated rows and sort by cell color

Re: Help please with excel VBA code

Re: Help please with excel VBA code

Re: Help please with excel VBA code

Re: Help please with excel VBA code

Re: Help please with excel VBA code

Re: Remove duplicated rows and sort by cell color

Re: Remove duplicated rows and sort by cell color

Re: Remove duplicated rows and sort by cell color

Re: Remove duplicated rows and sort by cell color

Hi, try this ...

And this !

Re: Remove duplicated rows and sort by cell color

Re: Remove duplicated rows and sort by cell color

Re: Remove duplicated rows and sort by cell color

Thread Information

Users Browsing this Thread

Similar Threads

VB Code in Outlook VB code to delete excel files from certain folder

[SOLVED] Excel VB code. Message pops up while code running asking question. Code must not wait.

[SOLVED] Excel Macro Visual Basic code not looking at all sheets with second section of code.

sir how to vlookup formula two line break in excel. sir my code is, this code is working a

Adding Text to another cell VB code (Help Tweeking code) (Excel 2007)

Code for email alerts from excel isn't working, wrong code possibly?

Importing Excel Macros Code from another Excel sheet which has Macro code.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions