+ Reply to Thread
Results 1 to 7 of 7

Double row check & Range definition using variable

  1. #1
    Registered User
    Join Date
    05-18-2015
    Location
    Bratislava
    MS-Off Ver
    Office 2007
    Posts
    33

    Double row check & Range definition using variable

    Hi guys.
    Would you please help me with one Macro ? Below is Macro which works very good for DELETION of duplicit
    rows. But I would like to do 2 things :

    Please Login or Register  to view this content.

    1. Is possible somehow to change this macro, so that duplicit rows are NOT deleted but rather HIGHLIGHTED
    with some color
    ? I could saw some macros somewhere, but there are tooo long. Below is short and readable
    so I hoped that it can be done shortly also for HIGHLIGHTING option.

    2. In this part of code >>
    Please Login or Register  to view this content.
    I understand that definition of my area is not so smart. Do you know why this is not working >>
    Range (BBB & 99999) ? Variable I got using Mid function >>

    Please Login or Register  to view this content.
    Thanks a lot for any help.
    ------------------------------------------------------
    Last edited by Rudo123; 05-21-2015 at 04:51 PM.

  2. #2
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Double row check & Range definition using variable

    Welcome Rudo123,
    You read the rules and you know you have to use code tags when posting code, please add then in your thread.
    " Is possible somehow to change this macro, so that duplicit rows are NOT deleted but rather HIGHLIGHTED
    with some color "
    I don't think it is possible, we need to prepare another macro, not a big deal but easier to do with a Excel sample.
    in
    Please Login or Register  to view this content.
    is BBB as variable ?
    What do you want to achieve ?
    - Battle without fear gives no glory - Just try

  3. #3
    Registered User
    Join Date
    05-18-2015
    Location
    Bratislava
    MS-Off Ver
    Office 2007
    Posts
    33

    Re: Double row check & Range definition using variable

    Hi PSI.
    thanks a lot for help and also alert, frankly, I didn't read rules, but I have already updated it.
    Thanks for replying on 2. question, it works now very well.
    Please, can you help with first question ? I suppose there must be some Do Until Loop cycle together maybe with For cycle. I will try to write it by myself and paste a code here. My goal with writing macros is always to write it as short as possible, thus I hoped that there is some method for HIGHLIGHTING double rows as well.

  4. #4
    Registered User
    Join Date
    05-18-2015
    Location
    Bratislava
    MS-Off Ver
    Office 2007
    Posts
    33

    Re: Double row check & Range definition using variable

    Hi. Here is my solution for only one column, I hope I will be able to do it also for more columns, If not, I will certainly ask you for help here
    I know that below is not the most suitable solution (for example I didn't ave to use array), but it works :-) What it does ? Sub will just highlight all cells/rows which are duplicite. But not both but always the second one, third one etc. So one row is always not formated. Maybe somebody will find it useful.But my goal is do it for more columns :-)

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    05-18-2015
    Location
    Bratislava
    MS-Off Ver
    Office 2007
    Posts
    33

    Re: Double row check & Range definition using variable

    Guys, I am trying and trying to do macro for double row check for more than 1 column, but no success :-( What I did is that I added all cells in my data range into 2 dimensional array. Now my strategy is to add all cells in one row (to concatenate them) into another array. So all concatenated cells in row1 will be array(1), all cells in row 2 will be array(2) etc. If I do this, then I can apply metod as above (double row check for 1 column only).

    Please, can you help me how can I create such array ? I just basically don't know how to concatenate all cells in one row, presumption is that number of columns can differ, so I can't state, first row is from column A to F.....F will be changing based in input.

    any help is very much appreciated.

  6. #6
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Double row check & Range definition using variable

    Please attach a sample file showing how is your data

  7. #7
    Registered User
    Join Date
    05-18-2015
    Location
    Bratislava
    MS-Off Ver
    Office 2007
    Posts
    33

    Re: Double row check & Range definition using variable

    Hi PCI. I did it Little bit funny, but it works :-)
    I added all rows which consist of several cells into dynamic array. This concatination of cells (A2,B2,C2 etc) in one row is added to new column....I simply had to add those concatinations into new column. Then I was able to apply double row check on this new column. All duplicite rows (not all, but always second, third etc.) are highlighted with yellow color. Based on this check in new column, I could highlight also all other cells (rows) in previous columns which create my main range. At the end I deleted this new column as it was just temporary.
    I know it's maybe not so good solution, but I wasn't able to find something else. If you think, it can be made much easier, please, let me know.
    Please, be aware that I am stil new in macros and thus code which can be written in 10 lines, I write in 20 lines...thus below code is probably so long.

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Equality of a range and a double variable in VBA
    By Haleh in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-26-2014, 08:08 AM
  2. Variable Last Row Definition
    By NCF in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-29-2013, 04:20 PM
  3. [SOLVED] Variable definition problem
    By Orestees in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-17-2012, 02:46 PM
  4. OleoObject name variable definition
    By mmf in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-05-2008, 05:09 PM
  5. Excel question with variable definition
    By Yong Kim in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-04-2005, 02:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1