+ Reply to Thread
Results 1 to 9 of 9

Macro to select visible rows containg data in filtered list

  1. #1
    Registered User
    Join Date
    07-12-2013
    Location
    Ottawa
    MS-Off Ver
    Excel 2010
    Posts
    22

    Thumbs up Macro to select visible rows containg data in filtered list

    Hi,

    I am new to the forum as this is my first post. I'm looking for help on a macro that I have been beating myself up over. I work on an excel file that updates daily by calling upon other programs to return data from the appropriate queries. I have header cells located in row# 11 which are constant. The amount of rows may or may not increase daily changing the location of data constantly (ex. row 30 moves to 31).

    I am looking to create a macro that will:

    1. Select the filtered visible rows containg data in column "A",
    2. Fill the selected rows with a colour (colour coding purposes)
    3. Input value (Y) into column "DK"

    I have attached two photos to provide a look at the type of excel file I'm working with.
    Excel forum 2.png
    Excel Forum 1.png

    I was using a If-Then-Else with a loop construct but I have had no luck. My second choice was a For-Next loop and I can't seem to define the statement correctly.
    Below is what I started with and no success. Any help would be much appreciated, thanks.

    Please Login or Register  to view this content.
    Last edited by knevil; 07-15-2013 at 03:50 PM.

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Macro to select visible rows containg data in filtered list

    Hi, knevil,

    welcome to Excel Forum. We would like to help you with your problem but sadly you missed to wrap your code with code-tags. Iīm pretty sure help will come after you have added these tags.

    BTW: most of the users here would prefer a sample workbook with a couple of lines of data over any picture.

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  3. #3
    Registered User
    Join Date
    07-12-2013
    Location
    Ottawa
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Macro to select visible rows containg data in filtered list

    Disreagard this thread
    Last edited by knevil; 07-12-2013 at 03:52 PM.

  4. #4
    Registered User
    Join Date
    07-12-2013
    Location
    Ottawa
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Macro to select visible rows containg data in filtered list

    Disreagard this thread
    Last edited by knevil; 07-12-2013 at 03:53 PM.

  5. #5
    Registered User
    Join Date
    07-12-2013
    Location
    Ottawa
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Macro to select visible rows containg data in filtered list

    Please find a sample workbook attached
    Attached Files Attached Files

  6. #6
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Macro to select visible rows containg data in filtered list

    Hi, knevil,

    okay, I downloaded the workbook and looked at it but Iīm afraid I canīt figure out the difference in Rows 49 to 51 opposed to Row 59 and Rows 68 to 69 which all show different colour for the same product and the same customer. As I canīt see why this is so Iīm afraid I canīt write any fitting code for your problem. Maybe you can help me out on that?

    Ciao,
    Holger

  7. #7
    Registered User
    Join Date
    07-12-2013
    Location
    Ottawa
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Macro to select visible rows containg data in filtered list

    Hi,

    Thank you for your help,

    I filter everything by vendor names so only certain rows are visible at one time. Different rows are visible at different times (Ex. Vendor A lassondre has data in rows 20-25. Vendor Art Craft has data from rows 26-30).

    I just need the macro to call on the filtered visible rows of data (sorted by vendor name). Different colours are added after the fact.

    I have attached a sample book that might help you better understand what I am looking for.

    The vendor is Alpen Secrets and when I received email confirmation from them, I filter by vendor name, select the visbile rows of data, colour grey, and then input "Y" into column FQ for the visible cells containg data.

    Sorry for the confusion, hope this helps
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    07-12-2013
    Location
    Ottawa
    MS-Off Ver
    Excel 2010
    Posts
    22

    Lightbulb Re: Macro to select visible rows containg data in filtered list

    Hi I was wondering if anyone had an idea on how to go about this macro? please see below
    Attached Files Attached Files

  9. #9
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Macro to select visible rows containg data in filtered list

    Hi, knevil,

    depending to the post #7 where a vendor is picked by filter maybe have a look at the code which will color the area from Column A to Column GJ and fill in Y in Column FQ:
    Please Login or Register  to view this content.
    Ciao,
    Holger

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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